https://blog.51cto.com/11103985/2330041
例子:
-------------------------------------------------
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
1) 内连接
select a.*,b.* from a join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2)左连接
select a.*,b.* from a
left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3) 右连接
select a.*,b.* from a
right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4) 全连接
select a.*,b.* from a
full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
交叉连接(CROSS JOIN)交叉连接(CROSS JOIN):有两种,显式的和隐式的,不带ON子句,返回的是两表的乘积,也叫笛卡尔积。这种是错误的我们要避免这种
语句1:隐式的交叉连接,没有CROSS JOIN。SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;
语句2:显式的交叉连接,使用CROSS JOIN。SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;
正确写法
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=C.ID;
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
ON (O.ID=C.ID);
语句7:WHERE条件独立。SQL> select s.sno,sname,score from student s LEFT JOIN sc on s.sno=sc.sno WHERE score <60;
SNO SNAME SCORE
---------- ------------------------------ ----------
s001 ???? 59
语句8:将语句7中的WHERE条件放到ON后面。SQL> select s.sno,sname,score from student s
LEFT JOIN
sc on s.sno=sc.sno AND score<60;
SNO SNAME SCORE
---------- ------------------------------ ----------
s001 ???? 59
s003 ????
s008 ????
s002 ????
s007 ????
s006 ????
s005 ????
s009 ??????
s004 ????
s010 ????
10 rows selected.
语句8:将语句7中的WHERE条件放到ON后面。SQL> select s.sno,sname,score from student s
WRGHT JOIN
sc on s.sno=sc.sno AND score<60;
从语句7和语句8查询的结果来看,显然是不相同的,语句8显示的结果是难以理解的。因此,推荐在写连接查询的时候,ON后面只跟连接条件,而对中间表限制的条件都写到WHERE子句中。
七、ON后面的条件(ON条件)和WHERE条件的区别:ON条件:是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件:在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出,将WHERE条件移入ON后面是不恰当的。推荐的做法是:
ON只进行连接操作,WHERE只过滤中间表的记录。