单行比较操作符

案例：

查询工资大于149号员工工资的员工的信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
        SELECT salary
        FROM employees
        WHERE employee_id = 149
        );

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
        SELECT job_id
        FROM employees
        WHERE employee_id = 141
        )
AND salary > (
        SELECT salary
        FROM employees
        WHERE employee_id = 143
        );

返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
        SELECT MIN(salary)
        FROM employees
        );

查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id。

SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
            SELECT manager_id
            FROM employees
            WHERE employee_id = 141
           )
AND department_id = (
            SELECT department_id
            FROM employees
            WHERE employee_id = 141
           )
AND employee_id <> 141;
# 法二：成对查询
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
                    SELECT manager_id,department_id
                        FROM employees
                    WHERE employee_id = 141
                   )
AND employee_id <> 141;