Zi 字媒體

資料庫(MySQL)各種JOIN 資料收藏 資料來源: https://mp.weixin.qq.com/s/gdo9hZezffhZeNubVxGe6g https://mp.weixin.qq.com/s/ItK2lmsawbcO2fa88bi6lg GITHUB:https://github.com/jash-git/Jash-good-idea-20190128-001 #下文将使用两个数据库表 Table_A 和 Table_B 来进行示例讲解，其结构与数据分别如下： mysql> SELECT * FROM Table_A ORDER BY PK ASC; +----+---------+ | PK | Value | +----+---------+ | 1 | both ab | | 2 | only a | +----+---------+ 2 rows in set (0.00 sec) mysql> SELECT * from Table_B ORDER BY PK ASC; +----+---------+ | PK | Value | +----+---------+ | 1 | both ab | | 3 | only b | +----+---------+ 2 rows in set (0.00 sec) 01.INNER 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 INNER JOIN Table_B B ON A.PK = B.PK; #查询结果： +------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ab | +------+------+---------+---------+ 1 row in set (0.00 sec) #注：其中 A 为 Table_A 的别名，B 为 Table_B 的别名，下同。 02.LEFT 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 LEFT JOIN Table_B B ON A.PK = B.PK; #查询结果： +------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ba | | 2 | NULL | only a | NULL | +------+------+---------+---------+ 2 rows in set (0.00 sec) 03.RIGHT 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 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) 04.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) 05.LEFT JOIN EXCLUDING INNER 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 LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL; #查询结果： +------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 2 | NULL | only a | NULL | +------+------+---------+---------+ 1 row in set (0.01 sec) 06.RIGHT JOIN EXCLUDING INNER 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 RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL; #查询结果： +------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | NULL | 3 | NULL | only b | +------+------+---------+---------+ 1 row in set (0.00 sec) 07.FULL OUTER JOIN EXCLUDING INNER 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 WHERE A.PK IS NULL OR B.PK IS NULL; #因为使用到了 FULL OUTER JOIN，MySQL 在执行该查询时再次报错。 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 WHERE A.PK IS NULL OR B.PK IS NULL' at line 4 #应当返回的结果（用 UNION 模拟）： mysql> SELECT * -> FROM Table_A -> LEFT JOIN Table_B -> ON Table_A.PK = Table_B.PK -> WHERE Table_B.PK IS NULL -> 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 | +------+--------+------+--------+ | 2 | only a | NULL | NULL | | NULL | NULL | 3 | only b | +------+--------+------+--------+ 2 rows in set (0.00 sec)