深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接-白红宇的个人博客

深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接

发布日期：2021-05-11 03:37:52 浏览次数：11 分类：博客文章

本文共 11073 字，大约阅读时间需要 36 分钟。

��SQL��-��

1���� = �� <> ��

�� students��courses��

2����

�� FROM��

1��LEFT JOIN��LEFT OUTER JOIN

�� LEFT OUTER��

2��RIGHT JOIN �� RIGHT OUTER JOIN

��

3��FULL JOIN �� FULL OUTER JOIN

��

3��
��

FROM ��

��

-------------------------------------------------

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 inner 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��2��

��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;

��1��2��

��INNER JOIN��

��INNER JOIN��

��3��4��

��3��INNER JOIN��

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME

FROM CUSTOMERS C,ORDERS O

WHERE C.ID=O.CUSTOMER_ID;

��4��INNER JOIN��ON��

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME

FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;

��3��4��

��OUTER JOIN����LEFT OUTER JOIN��RIGHT OUTER JOIN��FULL OUTER JOIN��

��

��=�� UNION ��

��LEFT OUTER JOIN��OUTER ��

��

��5��LEFT OUTER JOIN��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��6��RIGHT OUTER JOIN��

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��WHERE��ON��

��7��WHERE��

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';

��8��7��WHERE��ON��

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE_ORDER001';

��7��8��8��ON��WHERE��

��9��FULL OUTER JOIN��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��MySQL��Oracle��DB2��SQL��Oracle��

��10��9��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

UNION

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��9��10��

��UNION JOIN����Oracle��MySQL��

��11��UNION JOIN��SQL��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

��12��11��DB2��DB2��11��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

EXCEPT

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��13��11��Oracle��
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID

MINUS

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME

FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

��

��NATURAL INNER JOIN����SQL2��SQL��ON��*��ORACLE��NATURAL��

��14��
SELECT *

FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;

��15��

SELECT *

FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;

��16��

SELECT *

FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;

��17��

SELECT *

FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;

��SQL��

����WHERE��SELECT��

����ON��WHERE��SELECT��

����WHERE��SELECT��

��SQL��SQL��

��ON��ON��WHERE��

ON��

WHERE��ON��SELECT��ON��

��WHERE��ON��

ON��WHERE��

��

��SQL��

1��

2�� Col_L��Col_R��

3�� Col_R��Col_L��

4�� Col_R��Col_L��

5��

��

SELECT T1.C1,T2.CX,T3.CY

FROM TAB1 T1

INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)

INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)

LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);

WHERE T1.X >T3.Y;

��SQL��

上一篇：MySQL 中添加列、修改列以及删除列

下一篇：浅谈MySQL中的查询优化

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章