oracle sql 表连接复习

内连接
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id;

自然连接
SELECT e.employee_name,
d.department_name
FROM employees e
NATURAL JOIN departments d;

SELECT e.employee_name,
d.department_name
FROM employees e
JOIN departments d USING (department_id);

使用where连接两张表
SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id;

使用查找条件
SELECT d.department_name,
e.employee_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id >= 30；

SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND d.department_id >= 30;

自然连接使用查找
SELECT e.employee_name,
d.department_name
FROM employees e
NATURAL JOIN departments d
WHERE department_id = 20
ORDER BY e.employee_name;

自然 连接的错误写法
SELECT e.employee_name,
d.department_name
FROM employees e
NATURAL JOIN departments d
WHERE d.department_id = 20
ORDER BY e.employee_name;

内连接查找
SELECT e.employee_name,
d.department_name
FROM employees e
JOIN departments d USING (department_id)
WHERE department_id = 20
ORDER BY e.employee_name;

错误写法
SELECT e.employee_name,
d.department_name
FROM employees e
JOIN departments d USING (department_id)
WHERE d.department_id = 20
ORDER BY e.employee_name;

左外连接
SELECT d.department_name,
e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id >= 30;

SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND d.department_id >= 30;

SELECT d.department_name,
e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
AND e.salary >= 2000
WHERE d.department_id >= 30;

SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND e.salary (+) >= 2000
AND d.department_id >= 30

右外连接
SELECT d.department_name,
e.employee_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id >= 30;

全外连接

INSERT INTO employees VALUES (8888,‘JONES’,‘DBA’,null,to_date(‘02-1-1982’,‘dd-mm-yyyy’),1300,NULL,NULL);
COMMIT;

SELECT d.department_name,
e.employee_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;