单行操作符

• =

• != 或 <>

• =

• <

• <=

# 查询工资大于 149 号员工的员工信息
SELECT last_name
FROM employees
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE employee_id = 149
);

# 查询 job_id 与 141 号员工相同，salary 比 143 号员工多的 last_name，job_id 和 
# salary。
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
);

# 返回工资最少的员工信息
SELECT last_name, salary
FROM employees
HAVING salary = (
  SELECT MIN(salary)
  FROM employees
);

# 返回与 141 号员工的 manager_id 和 deparment_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;

# 查询最低工资大于 50 号部门最低工资的部门id 和 其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
  SELECT MIN(salary)
  FROM employees
  WHERE department_id = 50
);

# 显示员工的 employee_id，last_name 和 location
# 若员工的 deparment_id 与 location_id 为 1800 的 department_id 相同，
# 则 location 为 'Canada'，其余为 'USA'。
SELECT employee_id, last_name,
CASE
WHEN department_id = (
  SELECT department_id
  FROM departments
  WHERE departments.location_id = 1800
)
THEN 'Canada'
ELSE 'USA'
END 'location'
FROM employees e;

# 空值问题
# 没有结果，因为子查询没有结果。
SELECT last_name, job_id
FROM employees
WHERE job_id = (
  SELECT job_id
  FROM employees
  WHERE last_name = 'foo'
);

# 非法使用子查询
# 子查询的结果是多个，不知道该等于哪个，会报错。
SELECT last_name, salary
FROM employees
WHERE salary = (
  SELECT MIN(salary)
  FROM employees
  GROUP BY department_id
);