Join 联接 介绍

1） 在WHERE子句中指定联接
下例使用WHERE子句进行表之间的

SELECT A.SYMBOL,A.SNAME,B.TDATE,B.CLOSE

FROM SECURITYCODE A,DAYQUOTE B

WHERE A.SYMBOL =B.SYMBOL

    AND B.TDATE >= A.LISTDATE

    AND A.SYMBOL LIKE '600%'

在上例中联接的表A与表B通过A.SYMBOL =B.SYMBOL这个条件联接，筛选条件为B.TDATE不小于A. LISTDATE。

在WHERE子句中指定联接，对于较简单的联接，使用这种方式可能较方便，但综合来说，不推荐使用该语法联接表。

2）在FROM子句中指定联接
拿上面的例子来详细说，表SECURITYCODE中主要存放证券代码的基本信息，表中的SYMBOL代表股票代码，SNAME代表股票名称，LISTDATE代表上市日期；表DAYQUOTE中主要存放股票的每日行情。SYMBOL代表股票代码，TDATE代表交易日期， CLOSE代表收盘价。

我现在的取值逻辑是：取出股票代码以600打头的股票自上市日期以来的所有交易日的收盘价。将SECURITYCODE与DAYQUOTE通过SYMBOL联接。

SELECT A.SYMBOL,A.SNAME,B.TDATE,B. CLOSE

FROM SECURITYCODE A

JOIN DAYQUOTE B

    ON A.SYMBOL =B.SYMBOL

WHERE B.TDATE >= A.LISTDATE

    AND A.SYMBOL LIKE '600%'

ORDER BY A.SYMBOL,B.TDATE

对于使用FROM子句方式联接表，可以很清楚的看出表之间的联接条件。就可读性以及后续的可修改性与WHERE子句相比有较大的优势。

 

3）下面就联接的方式引用帮助文件中的具体介绍，联接可以分为以下几种：

1.内联接
   内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。

   内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

   在 SQL-92 标准中，内联接可在 FROM 或 WHERE 子句中指定。这是 WHERE 子句中唯一一种 SQL-92 支持的联接类型。WHERE 子句中指定的内联接称为旧式内联接。

内联接一般常见写法：

SELECT A.COLUMN1,[A.COLUMN2],B.COLUMN1,[B.COLUMN2]

FROM TABLE1 A

[INNER] JOIN TABLE2 B

    ON A.COLUMN0 = B.COLUMN0

在查询分析器中使用INNER JOIN时常常省略INNER。

2. 外联接
外联接可以是左向外联接、右向外联接或完整外部联接。

在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：

 

LEFT JOIN 或 LEFT OUTER JOIN

左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

 

RIGHT JOIN 或 RIGHT OUTER JOIN

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

 

FULL JOIN 或 FULL OUTER JOIN

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

 

仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。

外联接的中常见的是LEFT JOIN，将LEFT JOIN用熟已经可以解决大半问题了。

外联接的一般写法：

SELECT A.COLUMN1,[A.COLUMN2],B.COLUMN1,[B.COLUMN2]

FROM TABLE1 A

LEFT|RIGHT|FULL [OUTER] JOIN TABLE2 B

    ON A.COLUMN0 = B.COLUMN0

在查询分析器中使用OUTER JOIN时常常省略OUTER。LEFT和RIGHT只是方向问题，在特定场合下，FULL OUTER JOIN 相当于LEFT OUTER JOIN 和 RIGHT OUTER JOIN的消除重复行的合集。

3. 交叉联接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。也就是说在没有WHERE子句的情况下，若表A有3行记录，表B有6行记录:：

SELECT A.*,B.* FROM 表A CROSS JOIN 表B

那以上语句会返回18行记录。

3) join 方式
（1) cross join
      参与select语句所有表的的所有行的笛卡尔乘积
      select au_lname ,title
      from authors cross join titiles
     outer join 对参与join的两个表有主从之分，处理方式以主表的每条数据去match 从属表的列，合乎条件的数据是我们所要的答案，不合乎条件的也是我们要的答案，只不过哪些从属表选取的列将被添上null。
(2) left join
     左边的为主表，右边为从属表
     select a.cust_id ,b.order_date,b.tot_ant
     from customer a left join sales b
     on (a.cust_id =b.cust_id and b.order_date>''1996/10/15'')
     可以写为
     select a.cust_id,b.order_date,b.tot_ant
     from custom a
     left join (select * from sales where order_date>''1996/10/15'') b
    on a.cust_id =b.cust_id  
(3) right join
     左边的表为从属表，右边的表为主表
(4) self join
     self join 常用在同一表内不同数据间对同一列的比较
    select a.emp_no,a.emp_name,b.emp_no,b.emp_name,a.date_hired
    from employee a
    join employee b
    on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
    order by a.date_hired
   这样会重复数据，只要加上一句 and a.emp_name>b.emp_name
(5) full join
     不仅列出符合条件的数据，两边未符合join条件的数据也会一并列出。哪些未符合join条件的数据如果在select列中无法得到对应的值则填上null
    select a.cust_id,b.tot_amt
    from customer a full join sales b
    on a.cust_id=b.cust_id
有表
id   ic name amount
      I    *        *
      c
      i
      c
      i
      i  
   要求结果为
    ic name amount ic   name amount
    i                         c
    i                         c
    i
    i
select aaa.*,bbb.*
from ( select (select count(id) from aa as b where (b.id<a.id) and (ic=''i'')) as     newid, * from aa a where ic=''i'') aaa
full join
   (select (select count(id) from aa as b where b.id<a.id and ic=''c'') as newid,* from
aa a where ic=''c'') bbb
on aaa.newid=bbb.newid
order by aaa.name
6.使用 HASH 和 MERGE 联接提示
此示例在 authors、titleauthors 和 titles 表之间建立三表联接，以生成一个作者及其著作的列表。查询优化器使用 MERGE 联接将 authors 和 titleauthors (A x TA) 联接在一起。然后，将 authors 和 titleauthors MERGE 联接 (A x TA) 的结果与 titles 表进行 HASH 联结以生成 (A x TA) x T。

重要 指定联接提示后，要执行 INNER JOIN 时 INNER 关键字不再为可选，而必须显式说明。

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + '' '' + LTRIM(a.au_lname)), 1, 25)
   AS Name, SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta
   ON a.au_id = ta.au_id INNER HASH JOIN titles t
   ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC

下面是结果集：

Warning: The join order has been enforced because a local join hint is used.
Name                      Title               
------------------------- --------------------
Abraham Bennet            The Busy Executive''s
Reginald Blotchet-Halls   Fifty Years in Bucki
Cheryl Carson             But Is It User Frien
Michel DeFrance           The Gourmet Microwav
Innes del Castillo        Silicon Valley Gastr
...                    ...
Johnson White             Prolonged Data Depri
Akiko Yokomoto            Sushi, Anyone?      

(25 row(s) affected)