MySQL5查询帮助查找连接的未验证行

Question

在下面的测试表中找到：

SELECT * FROM tbl_emp; // There must be few employee with no dept id (did)
+------+-------+------+
| eid  | ename | did  |
+------+-------+------+
|    1 | SCOTT |    2 |
|    2 | JAMES |    4 |
|    3 | BOND  |    1 |
|    4 | TIGER |    5 |
|    5 | CHIP  |    0 |
|    6 | DALE  |    0 |
|    7 | MARY  |    0 |
+------+-------+------+

SELECT * FROM tbl_dept;// There must be few depts which have no employee.
+-------+-------------+
| dptid | dname       |
+-------+-------------+
|     1 | HR          |
|     2 | IT          |
|     3 | ADMIN       |
|     4 | TRAVEL      |
|     5 | SALES       |
|     6 | FINANCE     |
|     7 | ENGINEERING |
+-------+-------------+

我想在一个查询中列出tbl_emp中没有部门的所有员工名称和tbl_dept中没有员工的所有dname，查询的方式如下：

DESIRED RESULTSET:
-------------------
ename  dname
CHIP    
DALE
MARY
       ADMIN
       FINANCE
       ENGINEERING
-------------------

我能做的就是：

SELECT ename FROM tbl_emp WHERE did NOT IN (SELECT dptid FROM tbl_dept);

和

SELECT dname FROM tbl_dept WHERE dptid NOT IN (SELECT did FROM tbl_emp);

请帮助在单个查询中同时选择dname和ename。

Answer 1

您可以使用UNION

SELECT ename, NULL dname 
FROM tbl_emp 
WHERE did NOT IN (SELECT dptid FROM tbl_dept)
UNION
SELECT NULL ename, dname 
FROM tbl_dept 
WHERE dptid NOT IN (SELECT did FROM tbl_emp);

• SQLFiddle Demo

更新1

您也可以使用LEFT JOIN而不是NOT IN (我更喜欢使用这个)

SELECT  a.ename, b.dname 
FROM    tbl_emp a 
        LEFT JOIN tbl_dept b
            ON a.did = b.dptid
WHERE   b.dptid IS NULL
UNION
SELECT  c.ename, d.dname 
FROM    tbl_dept d 
        LEFT JOIN tbl_emp c
            ON d.dptid = c.did
WHERE   c.did IS NULL

• SQLFiddle Demo