sql各种表连接

SQL几种常用连接方式（旧式写法）  
一、NATURAL JOIN（自然连接）  
    两张表通过NATURAL JOIN连接的时候，相当于有个隐含的WHERE子句，对两张表中同名的对应列相比较看是否相等。  
SQL> select * from emp natural join dept;  
返回14行数据 相当于select * from emp , dept where emp.depno = dept.depno;  
二、CROSS JOIN（创建笛卡尔积）  
    对两张表通过交叉联合产生第三张返回结果集的表。相当于普通的连接。如下返回56行=14*4  
select * from emp cross join dept;  
  
三、INNER JOIN（内连接）  
   内连接就相当于普通的CROSS JOIN，只是格式不一样，INNER JOIN在后面有一个ON子句（相当于WHERE）的搜索条件，用于过滤返回的行。  
四、OUTER JOIN （外连接）  
   select * from ta outer join tb on (ta.c1=tb.c1)  
    outer join告诉DBMS生成结果表，在此表中不仅带有相关(ta.c1=tb.c1)行对，而且还有来自两个源表中任一表的不匹配的行。  
  
  
五、LEFT OUTER JOIN（左连接） RIGHT OUTER JOIN（右连接）  
    select * from ta left outer join  tb on (ta.c1=tb.c1)  
      select * from ta right outer join tb on (ta.c1=tb.c1)  
      left outer join(left join) 告诉DBMS生成包括联合行和任何不匹配的行的结果表，但是不匹配的行系来自查询的FROM子句中LEFT OUTER JOIN关键词左边的表中。  
    right outer join(right join)与left outer join(left join)刚好相反。  
  
  
六、FULL OUTER JOIN（全连接）  
   FULL OUTER JOIN 返回组合了LEFT OUTER JOIN 和RIGHT OUTER JOIN结果的表  
  
  
Oracle中常用新式写法  
1. 相等连接  
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;  
  
  
2. 外连接  
◆左条件(+) = 右条件;  
代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。  
此时也称为"右外连接".另一种表示方法是:  
SELECT ... FROM 表1 RIGHT OUTER JOIN 表2 ON 连接条件  
◆左条件 = 右条件(+);  
代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。  
此时也称为"左外连接".  
SELECT ... FROM 表1 LEFT OUTER JOIN 表2 ON 连接条件  
3. 不等连接  
两个表中的相关的两列进行不等连接,比较符号一般为>,<,...,BETWEEN.. AND..  
SELECT EMPNO,ENAME,SAL,GRADE FROM SALGRADE,EMP  
WHERE EMP.SAL BETWEEN LOSAL AND HISAL;  
4. 自连接  
自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。  












简单连接
SELECT dept.*,emp.* FROM EMP,dept WHERE EMP.DEPTNO=dept.DEPTNO;


JOIN连接
FROM join_table1 join_type join_table2 
[ON (join_condition)]


对同一个表操作的连接又称做自连接。


join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。


内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。
根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
(一)内连接
　　内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：
　　1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
　　2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
　　3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：
　　SELECT * 
　　FROM authors AS a INNER JOIN publishers AS p 
　　ON a.city=p.city
又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：
　　SELECT a.*,p.pub_id,p.pub_name,p.country 
　　FROM authors AS a INNER JOIN publishers AS p 
　　ON a.city=p.city




外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)
和全外连接(FULL OUTER JOIN或FULL JOIN)三种。


与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、
右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
　　(二)外连接
　　内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。
而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，
而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
　　如下面使用左外连接将论坛内容和作者信息连接起来：
　　SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b 
　　ON a.username=b.username
　　下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：
　　SELECT a.*,b.* 
　　FROM city as a FULL OUTER JOIN user as b 
　　ON a.username=b.username
　　左连接：(也叫左外连接，给出两个表的配匹行，并且以左边的表为准，如果左边表有而右边表没有的行，
则在右边表的相应行选择的列显示为NULL，允许左边的基准表对应右边表多条满足条件的记录)左连接就是返回左边的匹配行，
不考虑右边的表是否有相应的行，如：select field1,field2 from table1 left join table2 
on field1=field2(基准字段，可以多个)　where table1.field3=table2.field3
　　右连接：(也叫右外连接，给出两个表的配匹行，并且以右边的表为准，如果右边表有而左边表没有的行，
则在右边表的相应行选择的列显示为NULL，允许右边的基准表对应左边表多条满足条件的记录)


交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，
其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
(三)交叉连接
　　交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，
返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
　　例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。


　　SELECT type,pub_name 
　　FROM titles CROSS JOIN publishers 
　　ORDER BY


连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。


无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：


　　SELECT p1.pub_id,p2.pub_id,p1.pr_info 
　　FROM pub_info AS p1 INNER JOIN pub_info AS p2 
　　ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)