SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id;
非等值连接:连接条件使用的是不是=
SELECT
e.employee_id,
e.last_name,
e.department_id,
e.salary
FROM
employees e,
job_grades j
WHERE
e.salary BETWEEN j.lowest_sal
AND j.highest_sal;
自连接和非自连接
自连接:同一种表自己与自己进行连接
SELECT
emp.employee_id,
emp.last_name,
emp.manager_id,
mgr.last_name
FROM
employees emp,
employees mgr
WHERE
emp.manager_id = mgr.employee_id;
非自连接:两张不同的表进行连接
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id;
内连接和外连接
内连接
格式1
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id;
格式2
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
INNERJOIN departments d ON e.department_id = d.department_id;
格式3
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
JOIN departments d ON e.department_id = d.department_id;
多表内连接的写法一:
SELECT
e.employee_id,e.last_name,e.department_id,e.salary,l.location_id
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;
多表内连接的写法二:
SELECT
e.employee_id,
e.last_name,
e.department_id
FROM
employees e
INNERJOIN departments d
INNERJOIN jobs j ON e.department_id = d.department_id
AND e.job_id = j.job_id;
外连接
左外连接
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTJOIN departments d ON e.department_id = d.department_id;
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTOUTERJOIN departments d ON e.department_id = d.department_id;
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTJOIN departments d ON e.department_id = d.department_id
WHERE
ISNULL(e.department_id);
右外连接
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
RIGHTJOIN departments d ON e.department_id = d.department_id;
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
RIGHTOUTERJOIN departments d ON e.department_id = d.department_id;
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTJOIN departments d ON e.department_id = d.department_id
UNIONSELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTJOIN departments d ON e.department_id = d.department_id;
UNION ALL:不会执行去重操作
SELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
LEFTJOIN departments d ON e.department_id = d.department_id
WHERE
e.department_id ISNULLUNIONALLSELECT
e.employee_id,
e.first_name,
e.department_id
FROM
employees e
RIGHTJOIN departments d ON e.department_id = d.department_id
WHERE
e.department_id ISNULL;
NATURAL JOIN
NATURAL JOIN:自然连接,它会帮你自动查询两张连接表中所有相同的字段,然后进行等值连接`
SELECT
employee_id,
last_name,
department_name
FROM
employees e
NATURALJOIN departments d;
相当于如下sql:
SELECT
employee_id,
last_name,
department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;
USING
只能用在两张表的同名字段进行等值连接。如果连接条件的字段不同名 (如,自连接) ,就无法使用 USING 连接
SELECT
e.employee_id,
e.last_name,
e.department_id
FROM
employees e
JOIN departments d USING(department_id);