连接查询详解

                                                                    连接查询的语法规则
            之所以用连接查询，是因为连接查询可以进行几个表的组合操作。
将两个表按照一定的方式连接在一起时，两个表必须有公共的数据。如，A表中的a列对应于B表中的a列。
用于连接的列必须具有相同的数据类型，或可以自动进行类型转换。
在进行连接时，还应该指定连接条件。
连接的列名可以不同，如果使用相同名称的列必须为其加上作为限制，不然会产生混淆。
连接的过程大致如下：
1.为连接表生成笛卡尔积。
2.使用WHERE子句从形成的笛卡尔积中去除所有不符合限制条件的行。这里SELECT子句还没有执行。这也是
为什么WHERE子句中可以使用不包含在SEELCT列表中的列的原因。
3.使用WHERE子句进行删除后，如果语句中包含了GROUP BY子句，将按照GROUP BY子句中指定的列对剩下的行
进行分组。
4.SELECT子句将被应用到余下的数据中，表达式也被执行，选出在SELECT列表中的列。
连接有多种类型，一般有：自连接，内连接，外连接和交叉连接。
连接语法一般分为传统的连接语法和SQL連接语法。
SQL连接语法：
SELECT select_list
FROM Table1name[CROSS | NATURAL] JOIN Table2name
[ON[Table1name.]column operator[Table2name.column]
[WHERE conditions]
select_list是选择的列的名称，如果使用两个表中有相同名称的列，则必须限定是哪个
表中的例。
单独使用JOIN关键字时，需要使用ON关键字来设定连接条件。
CROSS与NATURAL是可选项，如果选择了CROSS或NATURAL关键字，就不能使用ON关键字。
因此，必须用WHERE子句设定连接的条件。否则返回的结果会相当多（笛卡尔积）。

下面用JOIN进行连接，查询employees表中的emp_id,fname,job_id列和jobs表中的job_desc列，然后用job_id列连接employee表和jobs表:

SELECT employee.emp_id,fname,employee.job_id,jobs.job_desc
FROM employee JOIN jobs 
ON employee.job_id=jobs.job_id  /**//*JOIN单独使用时，要用ON关键字来设定连接条件*/

将上面的例子改为CROSS JOIN，产生的结果一样：

SELECT employee.emp_id,fname,employee.job_id,jobs.job_desc
FROM employee CROSS JOIN jobs /**//*有CROSS关键字时，用WHERE设定连接条件*/
WHERE employee.job_id=jobs.job_id 

使用连接中的运算符
在进行连接时，可以使用运算符指定连接的条件(可以使用大于或小于等运算符)。如下例：

SELECT employee.emp_id,fname,employee.job_id,jobs.job_desc
FROM employee CROSS JOIN jobs /*使用了交叉连接，下面会有介绍*/
WHERE employee.job_id > 13 AND employee.fname > jobs.job_desc

                                                                    ＜自连接＞

自连接就是一个表的两个副本之间的连接，使用它可以将同一个表的不同数据行连接起来。使用
自然连接要注意由于是将表与其自身相连接，因此必须对表设定别名。

　　我们在这里同样给出一个表来对自连接进行解释。
　　深南路公交线路表：
　　route(num, company, pos, stop)

SELECT * FROM route R1, route R2 WHERE R1.num=R2.num AND R1.company=R2.company

我们route表用字段(num, company)来进行自连接． 结果是什么意思呢？
你可以知道每条公交线路的任意两个可联通的车站。

用stop字段来对route（公交线路表）进行自连接。

SELECT * FROM route R1, route R2 WHERE R1.stop=R2.stop;

询的结果就是共用同一车站的所有公交线。这个结果对换乘是不是很有意义呢。


　　　　                                             <内连接>
在两个表中采用内连接，以返回与所要连接的列上相匹配的所有行。
内连接又分为等值连接，自然连接和不等连接三种。
1)等值连接
等值连接指在连接条件中使用等于(=)运算符比较被连接列的列值。其查询结果中列出被
连接表中的所有列，包括重复列。
如列出OrderId,EmployeeId和下订单的雇员名字：

SELECT ORD.OrderID,ORD.EmployeeId,EMP.LastName 
FROM Orders ORD
INNER JOIN Employees EMP
ON ORD.EmployeeId=EMP.EmployeeID
ORDER BY EMP.LastName

首先，SELECT ORD.ORDERID,ORD.EmployeeID,EMP.LastName这个语句列出所需要的列名，
即Orders表中的OrderId和EmployeeID,Employees表中的LastName。
FROM Orders ORD列出用于从中提取数据的表之一。
INNER JOIN语句命名其他表:INNER JOIN Employees EMP
接下来，命名希望与表连接的列。我们为每个表只命名了一个列，但可以命名多个列，可以
使用方括号，AND和OR完成希望完成的实际连接。
ON ORD.EmployeeId=EMP.EmployeeID
ORD和EMP是Orders和Employees表的别名，这样就可以容易地直接引用表而不是每次写出整个名称。
ORDER BY子句：ORDER BY EMP.LastName以LastName的顺序列出结果。默认是升序。

_{2)不等连接}
不等连接的运算符包括：>,>=,<=,<,!>,!<和<>。
下面从student和sc表中使用不等值连接查询每个学生及其选修课程的情况：

SELECT Student.sno,Student.name,Student.dept,sc.* 
FROM Student INNER JOIN sc
ON Student.Sno>sc.Sno/**//*ON子句定义连接本身的列名称*/

_{3)自然连接}
自然连接是一种特殊的等值连接。如果是按照两个表中的相同属性进行等值连接，且目标
列中去掉了重复的列，保留了的所有不重复的列。自然连接只有在两个表有相同名称的列且列的含义相似
时才能使用，将在同名列上进行自然连接。
下面对student表和sc表使用自然连接,由于name,sex,age,dept,cno和grade列在student表
和sc表中是不重复的，因此，引用时可以去掉前缀，而sno列在两个表中都出现了，引用时必须加上前缀：

SELECT Student.sno,name,sex,age,dept,cno,grade
FROM Student INNER JOIN sc
ON Student.sno=sc.sno

                                                                <外连接>
前面介绍的连接都是比较来自FROM子句中表的每一记录，并返回所有满足连接条件的记录。
而有时候可能希望显示某个表中所有记录，包括不符合条件的记录，此时就要用外连接。使用外连接可以方便地连接结果中包含某个表中的其他记录。外连接的查询结果是内连接查询结果的扩展。外连接的特点就是某些不满足连接条件的数据也可以出现在连接结果中。普通连接操作只输出满足连接条件的数据。外连接操作以指定表为连接主体，将主体表中不满足连接条件的数据一并输出。
外连接的分类
　　外连接又分为左外连接(LEFT OUTER JOIN或LEFT JOIN)，右外连接(RIGHT OUTER JOIN)
和全外连接(FULL OUTER JOIN或FULL JOIN)三种。
　左外连接表示在结果中包括了左表中不满足条件的数据。左外连接就是将左表的所有数据分别与右表的每条数据进行连接组合，返回的结果除内连接的数据外，还有左表中不符合条件的数据，并在右表的相应列中填上NULL值。
　右外连接表示在结果中包括了右表中不满足条伯的数据。
　如果左表和右表中不满足条件的数据都出现在结果中，那么这种连接是全外连接。
外连接返回查询结果中的不仅包含符合条件的行，而且包括左表（左外连接时），右表（右外连接时）或两个連接表（全外连接）中的所有不符全连接条伯的数据行。在连接语句中，JOIN关键字的左边的表称为左表，右边的表称为右表。
你可能还是想知道，这样讨厌的家伙到底有什么用，看看如下情况：
比较顾客表和订单表，找到所有顾客的列表，看是否下了订单。使用上面的内连接，只会看到列出
那些已经下了订单的顾客。这时，用外连接就可以看到所有雇员的列表，包括那些必须下订单的顾客。这允许检查誰在卖货，
誰没有卖货。
_{1)左外连接}

SELECT EMP.LastName,ORD.OrderId FROM Employees EMP
LEFT OUTER JOIN Orders ORD
ON EMP.EmployeeId=ORD.EmployeeId
ORDER BY EMP.EmployeeID

我们使用LEFT OUTER JOIN是因为Employees是左侧表，LFET帮我们找出所有没有在Orders表中出现的记录。建议读者在Employees(作为左表)添加几条记录，用上面的语句，就可以看出所述效果。
此时还会发现，对应于所添加的几条记录中，OrderId里面的值为NULL。
_{2)右外连接}
右外连接与左外连接相似，但作用正好相反。右外连接就是将右表的所有数据分别与左
表的每条数据进行连接组合，返回的结果除内连接的数据外，还有右表中不符合条件的数据，
并在左表的相应列中填上NULL值。
下面将book数据庫的student表和sc表中使用右外连接查询每个学生及其选修的情况，
包括选修课程的所有学生。

SELECT name,sex,age,dept,cno,grade
FROM Student RIGHT JOIN sc /**//*返回包括右表所有不符合条件的记录*/
ON Student.sno=sc.sno

_{3)全外连接}
如果希望在连接结果中列出连接的两个表中包含的所有记录，则可以使用全外连接。
全外连接就是将左表的所有数据分别与右表的每条数据进行连接组合，返回的结果除内连接
的数据处，还有两个表中不符合条件的数据。并在左表或右表的相应列中填上NULL值。
下面下面将book数据庫的student表和sc表中使用全外连接查询每个学生及其选修的情况，
以及表中不满足条件的所有数据。

   SELECT name,sex,age,dept,cno,grade
    From Student FULL JOIN sc
    ON Student.sno=sc.sno  /**//*返回包括左右两个表中所有不符合件的记录*/

                                            <交叉连接>
交叉连接在返回左侧表中的每个行时，会例出右侧表中的每个行。如果一个表中有1000行，另一个
表有500行，则结果集返回1000*500个行，总共500,000个行。

SELECT LastName 
FROM Employees 
cross join Orders