|
示例查询：
- SELECT A.PK AS A_PK, B.PK AS B_PK,
- A.Value AS A_Value, B.Value AS B_Value
- FROM Table_A A
- RIGHT JOIN Table_B B
- ON A.PK = B.PK;
查询功效：
- +------+------+---------+---------+
- | A_PK | B_PK | A_Value | B_Value |
- +------+------+---------+---------+
- | 1 | 1 | both ab | both ba |
- | NULL | 3 | NULL | only b |
- +------+------+---------+---------+
- 2 rows in set (0.00 sec)
4、FULL OUTER JOIN
FULL OUTER JOIN 一样平常被译作外毗连、全毗连，现实查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外毗连查询能返回阁下内外的全部记录，个中阁下内外能关联起来的记录被毗连后返回。
文氏图：
FULL OUTER JOIN
示例查询：
- SELECT A.PK AS A_PK, B.PK AS B_PK,
- A.Value AS A_Value, B.Value AS B_Value
- FROM Table_A A
- FULL OUTER JOIN Table_B B
- ON A.PK = B.PK;
查询功效：
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B
- ON A.PK = B.PK' at line 4
注：我当前示例行使的 MySQL 不支持 FULL OUTER JOIN。
该当返回的功效(行使 UNION 模仿)：
- mysql> SELECT *
- -> FROM Table_A
- -> LEFT JOIN Table_B
- -> ON Table_A.PK = Table_B.PK
- -> UNION ALL
- -> SELECT *
- -> FROM Table_A
- -> RIGHT JOIN Table_B
- -> ON Table_A.PK = Table_B.PK
- -> WHERE Table_A.PK IS NULL;
- +------+---------+------+---------+
- | PK | Value | PK | Value |
- +------+---------+------+---------+
- | 1 | both ab | 1 | both ba |
- | 2 | only a | NULL | NULL |
- | NULL | NULL | 3 | only b |
- +------+---------+------+---------+
- 3 rows in set (0.00 sec)
小结
以上四种，就是 SQL 里常见 JOIN 的种类和观念了，看一下它们的合影：
小结
有没有感受少了些什么，学数学集适时完全不止这几种环境?确实云云，继承看。
延长用法
1、LEFT JOIN EXCLUDING INNER JOIN
返回左表有但右表没有关联数据的记录集。
文氏图：
LEFT JOIN EXCLUDING INNER JOIN
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
