department表
department_id
department_name
manager_id
location_id
employee表
employee_id
first_name
last_name
phone_number
job_id
salary
commission_pct
manager_id
department_id
location表
location_id
street_address
postal_code
city
state_province
country_id
内连接
合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
SELECT last_name,department_name,city
FROM employee emp JION department dep
ON emp.department_id=dep.department_id
JION location loc
ON dep.location_id=loc.location_id
外连接
合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表或右表中不匹配的行。没有匹配的行时,结果表中相应的列为NULL。
左外连接
SELECT last_name,department_name
FROM employee emp LEFT JION department dep
ON emp.department_id=dep.department_id
右外连接
SELECT last_name,department_name
FROM employee emp RIGHT JION department dep
ON emp.department_id=dep.department_id
满外连接(MySQL 不支持 FULL JOIN)
SELECT last_name,department_name
FROM employee emp FULL JION department dep
ON emp.department_id=dep.department_id
合并查询结果
利用UNION关键字 ,可以给出多条SELECT语句,并将它们的结果合成多个结果集。合并时,两个表对应的列数和数据类型必须相同,并且互相对应。各个SELECT语句之间使用UNION或NUION ALL关键字分割。
UNION
查询结果求并集,去重
左连接 UNION 右连接
SELECT last_name,department_name
FROM employee emp LEFT JION department dep
ON emp.department_id=dep.department_id
UNION
SELECT last_name,department_name
FROM employee emp RIGHT JION department dep
ON emp.department_id=dep.department_id
UNION ALL
查询结果求并集,不去重
左边部分 UNION ALL 右连接
SELECT last_name,department_name
FROM employee emp LEFT JION department dep
ON emp.department_id=dep.department_id
WHERE dep.department_id IS NULL
UNION ALL
SELECT last_name,department_name
FROM employee emp RIGHT JION department dep
ON emp.department_id=dep.department_id
左边部分
SELECT last_name,department_name
FROM employee emp LEFT JION department dep
ON emp.department_id=dep.department_id
WHERE dep.department_id IS NULL
右边部分
SELECT last_name,department_name
FROM employee emp RIGHT JION department dep
ON emp.department_id=dep.department_id
WHERE emp.department_id IS NULL
左右两边
SELECT last_name,department_name
FROM employee emp RIGHT JION department dep
ON emp.department_id=dep.department_id
WHERE emp.department_id IS NULL
OR dep.department_id IS NULL
SQL99 新特性1:NATURAL JOIN
natural会自动查询两张连接表中相同的字段,然后进行等值连接
SELECT employee_id,last_name,department_name
FROM employee emp NATURAL JOIN department d;
SQL99 新特性2:USING
using 指定了具体的相同的字段名称,需要在USING的括号中填入需要连接的同名字段
SELECT employee_id,last_name,department_name
FROM employee emp NATURAL JOIN department d
USING(department_id)