成对比较
SELECT employee_id,
manager_id ,
department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id,
department_id
FROM employees
WHERE employee_id IN (178,174)
)
AND employee_id NOT IN (178,174);
输出:176 149 80
只有要查询的东西和你子查询返回的东西一一对应上了,你的查询才能成功。
如果有一个 对应不上那么你的查询不会成功。
SELECT employee_id,
manager_id ,
department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id FROM employees WHERE employee_id IN (174,141)
)
AND department_id IN
(SELECT department_id FROM employees WHERE employee_id IN (174,141)
)
AND employee_id NOT IN(174,141);
输出:144 124 50
143 124 50
142 124 50
176 149 80
上面两个程序就是成对子查询和非成对子查询两者之间的区别。