SQL主要有两个标准: SQL92和 SQL99。
一般来说,SQL92的形式更简单,但SQL语句的可读性较差;SQL99相比于SQL92,语法更加复杂,但可读性更强。
SQL99使用JOIN...ON...
实现多表查询。
- 内连接,
JOIN...ON...
,同INNER JOIN...ON...
- 左外连接,
LEFT JOIN...ON...
,同LEFT OUTER JOIN...ON...
- 右外连接,
RIGHT JOIN...ON...
,同RIGHT OUTER JOIN...ON...
SQL99下的内连接
- 查询员工的id及其所在部门名称。
JOIN...ON...
。
INNER JOIN...ON...
,可简写为JOIN...ON...
# 查询员工的id及其所在部门名称
# JOIN...ON...
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id; -- 返回106行记录。
# INNER JOIN...ON...
SELECT employee_id,department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id; -- 返回106行记录。
- 查询员工的id、所在部门名称、所在城市。
JOIN...ON...JOIN...ON...
或者JOIN ... JOIN ...ON ...AND...
# JOIN...ON...JOIN...ON...
SELECT employee_id,department_name,city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id; -- 返回106行记录
# JOIN ... JOIN ...ON ...AND...
SELECT e.last_name,d.department_name,l.city
FROM employees e JOIN departments d JOIN locations l
ON e.department_id = d.department_id
AND d.location_id = l.location_id; -- 返回106行记录
SQL99下的外连接
左外连接
查询所有员工的id及其所在部门名称。LEFT JOIN...ON...
,左外连接。
LEFT OUTER JOIN...ON...
,可简写为LEFT JOIN...ON...
。
# LEFT JOIN...ON
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id; -- 返回107行记录
# LEFT OUTER JOIN...ON
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id; -- 返回107行记录
右外连接
查询所有部门及其所有员工的id。RIGHT JOIN...ON...
,右外连接。
RIGHT OUTER JOIN...ON...
,可简写为RIGHT JOIN...ON...
。
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;-- 返回122行记录
满外连接
SELECT employee_id,department_name
FROM employees e FULL JOIN departments d
ON e.department_id = d.department_id;
/*
报错:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN departments d
ON e.department_id = d.department_id' at line 2
*/
SQL99下满外连接,使用FULL JOIN ...ON
,但MySQL不支持SQL99满外连接的语法,Orcale支持这种语法。