多表连接

-- ANSI 标准写法（推荐）
SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON d.department_id = e.department_id
ORDER  BY e.employee_id;

-- Oracle 老式写法（等价）
SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id
ORDER  BY e.employee_id;

-- 左连接：保留 departments 的所有行，员工侧为 NULL 表示没人
SELECT d.department_id, d.department_name, e.employee_id, e.last_name
FROM   departments d
LEFT   JOIN employees e ON e.department_id = d.department_id
WHERE  e.employee_id IS NULL      -- 关键：用 IS NULL 反查出「没有」
ORDER  BY d.department_id;

-- (+) 放在「可能为空」的那一侧
SELECT d.department_id, d.department_name, e.last_name
FROM   departments d, employees e
WHERE  d.department_id = e.department_id(+)
ORDER  BY d.department_id;

SELECT e.employee_id   AS emp_id,
       e.last_name     AS employee,
       m.employee_id   AS mgr_id,
       m.last_name     AS manager
FROM   employees e
LEFT   JOIN employees m ON m.employee_id = e.manager_id
ORDER  BY e.employee_id;

SELECT e.last_name,
       d.department_name,
       l.city,
       c.country_name
FROM   employees   e
JOIN   departments d ON d.department_id = e.department_id
JOIN   locations   l ON l.location_id   = d.location_id
JOIN   countries   c ON c.country_id    = l.country_id
WHERE  c.region_id = 2
ORDER  BY e.last_name;

-- 用 WITH 现造一张工资等级表，避免依赖 SCOTT 方案
WITH salgrade AS (
  SELECT 1 AS grade,     0 AS losal,  2999 AS hisal FROM dual
  UNION ALL SELECT 2,  3000,  5999 FROM dual
  UNION ALL SELECT 3,  6000,  9999 FROM dual
  UNION ALL SELECT 4, 10000, 14999 FROM dual
  UNION ALL SELECT 5, 15000, 99999 FROM dual
)
SELECT e.last_name, e.salary, s.grade, s.losal, s.hisal
FROM   employees e
JOIN   salgrade s
       ON e.salary BETWEEN s.losal AND s.hisal
ORDER  BY s.grade, e.salary;

非等值连接的条件不是 =，而是 BETWEEN、<、> 等区间判断。这里用 WITH 临时造出等级表，所以在只装了 HR 方案的库里也能直接跑。

SELECT e.last_name, d.department_name
FROM   employees e
CROSS  JOIN departments d;
-- 107 × 27 = 2889 行，几乎总是写错了