exists返回结果为一个Boolean值
SELECT EXISTS(SELECT employee_id FROM employees );
#查询有员工名的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE d.`department_id`=e.`department_id`
)
先执行主句select
然后执行where的条件语句
上句可以用一下执行
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id` IN(
SELECT `department_id`
FROM `employees`
);
#查询没有女朋友的男神信息
SELECT bo.*
FROM `boys` bo
WHERE NOT EXISTS(
SELECT b.`boyfriend_id`
FROM `beauty` b
WHERE b.`boyfriend_id`=bo.`id`
);
等价于
SELECT bo.*
FROM `boys` bo
WHERE bo.`id` IN(
SELECT `boyfriend_id`
FROM `beauty`
);