exists后面

exists()判断结果是否有没有。只返回1/0。但是大部分都可以使用IN代替。

/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
-- 案例1：查询有员工的部门名
-- in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
    SELECT department_id
    FROM employees
)
-- exists
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id`=e.`department_id`
);
-- 案例2：查询没有女朋友的男神信息
-- in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)
-- exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.`id`=b.`boyfriend_id`
);