|
示例查询：
- 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)
小结 （编辑：西安站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
