数据库SQL实战之内连接(inner join)，左连接(left join)，右连接(right join)，全连接(full join)

Person 表

IDP	LastName	Firstname	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Firth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Orders表

IDO	OrderNo	IDP
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34762	65

full join :在两张表进行连接查询时，返回左表和右表中所有匹配的行

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.IDP=Orders.IDP
ORDER BY Persons.LastName 

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

inner join :在两张表进行连接查询时，只保留了两张表中完全匹配的结果集

SELECT Persons.LastName,Persons.FirstName,Orders.OrderNo
From Persons
INNER JOIN Orders
ON Persons.IDP = Orders.IDP
ORDER BY Persons.LastName

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

left join :在两张表进行连接查询时，会返回左表所有的行，即使在由表中没有匹配记录

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.IDP=Orders.IDP
ORDER BY Persons.LastName

LastName	FirstName	OrderNo
Adamas	John	22456
Adamas	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

right join : 在两张表进行连接查询时，会返回右表中所有的行，即使在左表中没有匹配记录

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.IDP=Orders.IDP
ORDER BY Persons.LastName

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

习题

• 题目描述

查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

elect s.*,d.dept_no 
from salaries as s 
join dept_manager as d
on s.emp_no = d.emp_no
where s.to_date = "9999-01-01"
and d.to_date = "9999-01-01"

• 题目描述
  查找所有已经分配部门的员工的last_name和first_name
  CREATE TABLE dept_emp (
  emp_no int(11) NOT NULL,
  dept_no char(4) NOT NULL,
  from_date date NOT NULL,
  to_date date NOT NULL,
  PRIMARY KEY (emp_no,dept_no));
  CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender char(1) NOT NULL,
  hire_date date NOT NULL,
  PRIMARY KEY (emp_no));

select e.last_name,e.first_name,d.dept_no 
from employees as e
inner join dept_emp as d
on e.emp_no = d.emp_no

• 题目描述
  查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
  CREATE TABLE dept_emp (
  emp_no int(11) NOT NULL,
  dept_no char(4) NOT NULL,
  from_date date NOT NULL,
  to_date date NOT NULL,
  PRIMARY KEY (emp_no,dept_no));
  CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender char(1) NOT NULL,
  hire_date date NOT NULL,
  PRIMARY KEY (emp_no));

select e.last_name,e.first_name,d.dept_no 
from employees as e
left join dept_emp as d
on e.emp_no = d.emp_no