mysql - sql查询以获取具有条件的行

Question

我想同时从表a中获取unique_key列在表b中的行，表c中不存在unique_key。所以如果我写表，

table a 
+----+------+-----------+
| id | Name | int_value |
+----+------+-----------+
| a1 | aa   |         1 |
| a2 | bb   |         2 |
| a3 | cc   |         5 |
+----+------+-----------+
table b
+----+------+-----------+
| id | Name |unique_key |
+----+------+-----------+
| a1 | aa   |        u1 |
| a2 | bb   |        u2 |
| a3 | cc   |        u5 |
+----+------+-----------+
table c 
+----+------+-----------+
| id | Name |unique_key |
+----+------+-----------+
| c1 | aa   |        u1 |
| c2 | bb   |        u2 |
| c3 | cc   |        u3 |
+----+------+-----------+

结果，我想得到

id name int_value b.unique_key
a3 cc   5         u5            

因为 u5 在表 c 中不存在。我尝试了这些 sql，但没有一个能得到正确的结果。你会怎么做？

SELECT a.*, b.unique_key
FROM a
LEFT JOIN ( SELECT id FROM b ) b
ON a.id = b.id
LEFT JOIN ( SELECT unique_key FROM c ) c
ON b.unique_key <> c.unique_key;

SELECT a.*, b.unique_key, c.unique_key
FROM a
LEFT JOIN ( SELECT id FROM b ) b
ON a.id = b.id
LEFT JOIN ( SELECT unique_key FROM c ) c
ON b.unique_key = c.unique_key
WHERE c.unique_key IS NULL;

标签： mysqlsqljoin

Answer 1

另一种方法是使用 NOT IN

SELECT a.id, a.name, a.int_value, b.unique_key
FROM a
JOIN b ON a.id = b.id
WHERE b.unique_key NOT IN (SELECT DISTINCT c.unique_key FROM c)

结果 id: a3, name: cc, int_value: 5, unique_key: u5