mysql+全外连接之排序_MySQL连接查询

SELECT  查询列表

FROM  table_a AS  别名 [连接类型]

JOIN table_b 别名

ON 连接条件

[WHERE 筛选条件]

[GROUP BY 分组]

[HAVING 分组筛选条件]

[ORDER BY 排序列表]

1.1、连接类型分类

内连接:INNER JOIN

外连接:

左外连接:LEFT [OUTER] JOIN

右外连接:RIGHT [OUTER] JOIN

全外连接:FULL [OUTER] JOIN

交叉连接:CROSS

2、数据库库表数据如下

3、各种连接查询解释及案例

3.1、INNER JOIN:内连接(查询的是两张表符合条件的公共部分)

3.1.1、基本语法

SELECT 查询列表

FROM 表A AS 别名 a

INNER JOIN 表 B AS 别名 b

ON 连接条件;

3.1.2、根据连接条件分类

等值连接

非等值连接

自连接

3.1.3、具体案例

1、显示所有员工的姓名,部门编号和部门名称(等值连接)

SELECT e.last_name,d.department_id,d.department_name

FROM employees e INNER JOIN departments d

ON e.department_id = d.department_id;

2、查询90号部门员工的job_id和90号部门的location_id(等值连接)

SELECT e.department_id,e.job_id,d.location_id

FROM employees e INNER JOIN departments d

ON e.department_id = d.department_id

AND e.department_id = 90;

语言

方法

3714

KGSPs2ThzP

2010.01.05 01-20-35

3、查询工作city在Toronto的员工的 last_name , job_id , department_id , department_name(等值连接)

SELECT e.last_name,j.job_id,e.department_id,d.department_name

FROM employees e INNER JOIN jobs j INNER JOIN departments d INNER JOIN locations l

ON e.job_id = j.job_id

AND e.department_id = d.department_id

AND d.location_id = l.location_id

WHERE l.city = 'Toronto';

4、查询每个工种、每个部门的部门名、工种名和最低工资(等值连接)

SELECT j.job_title,d.department_name,MIN(e.salary)

FROM employees e INNER JOIN departments d INNER JOIN jobs j

ON e.job_id = j.job_id

AND e.department_id = d.department_id

GROUP BY e.job_id,d.department_id;

5、查询员工的工资和工资级别并按照工资升序排序(非等值连接)

SELECT salary,grade_level

FROM employees e INNER JOIN job_grades j

ON salary BETWEEN lowest_sal AND highest_sal

ORDER BY salary ASC;

6、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式(自连接)

employee_name employee_id manager_name manager_id

kochhar 101 king 100

SELECT

e.last_name AS employee_name,

e.employee_id,

m.last_name AS manager_name,

m.manager_id

FROM employees e INNER JOIN employees m

ON e.employee_id = m.employee_id;

3.2、LEFT JOIN:左外连接(查询的是A表中有而B表中没有的部分 +  两张表的公共部分)

3.2.1、基本语法

SELECT 查询列表

FROM 表A AS 别名 a

LEFT JOIN 表 B AS 别名 b

ON 连接条件;

3.2.2、表结构与数据

            

3.2.3、具体案例

1、在执行左连接查询之前,先看看使用内连接查询出来的数据,如下图可以看到查出的是7条记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

INNER JOIN boys AS b

ON g.boyFriend_id = b.id;

2、接着以girls表作为主表进行左连接查询,如下图可以看到查询的记录是9条,可以看出1-7条记录就是INNER JOIN查询出来的记录,而第8条和第9条记录是主表(girls)中存在的记录而从表(boys)中不存在.

得出结论:左连接查询的结果  =  两表的交集部分(INNER JOIN的查询结果)  +  主表中存在的记录而从表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id;

3、最后以boys表作为主表进行左连接查询,如下图可以看到查询的记录是10条,可以看出1-7条记录就是INNER JOIN查询出来的记录,而第8条第9条和第10条记录是主表(boys)中存在的记录但是从表(girls)中不存在该记录.

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

INNER JOIN boys AS b

ON g.boyFriend_id = b.id;

得出结论:左连接查询的结果  =  两表的交集部分(INNER JOIN的查询结果)  +  主表中存在的记录而从表中不存在的记录

3.3、RIGHTJOIN:右外连接(查询的是B表中有而A表中没有的部分 + 两张表公共的部分)

右外连接和左外连接使用基本相似,可以通过变换主表和从表的位置来达到相同的效果,例如下面的SQL的效果是相同的

# 左外连接

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id;

# 右外连接

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM boys AS b

RIGHT JOIN girls g

ON g.boyFriend_id = b.id;

3.4、全连接(查询的是A表中存在而B表中不存在的记录 + B表中存在而A表中不存在的记录 + A表和B表的交集)

注:Oracle中有 full join,mysql中没有full join,但是我们可以通过union 或 union all来实现全连接

1、使用UNION进行全连接(union),可以看出数据是去除了重复值

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id

UNION

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM boys AS b

LEFT JOIN girls AS g

ON g.boyFriend_id = b.id;

2、使用UNION ALL进行全连接(union all),可以看出数据没有去除了重复值,简单的将A表和B表合并在一起了

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id

UNION ALL

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM boys AS b

LEFT JOIN girls AS g

ON g.boyFriend_id = b.id;

3.5、其它常用的连接查询形式

1、查询出A表中存在而B表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id

WHERE b.id IS NULL;

2、查询出B表中存在而A表中不存在的记录

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM boys AS b

LEFT JOIN girls AS g

ON g.boyFriend_id = b.id

WHERE g.boyFriend_id IS NULL;

3、查询出A表和B表去除了交集以后的并集部分

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM girls AS g

LEFT JOIN boys AS b

ON g.boyFriend_id = b.id

WHERE b.id IS NULL

UNION

SELECT g.id,g.name,g.age,g.boyFriend_id,b.id,b.name,b.age

FROM boys AS b

LEFT JOIN girls AS g

ON g.boyFriend_id = b.id

WHERE g.boyFriend_id IS NULL;

外连接总结:

外连接查询的结果是主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值,如果从表中没有和它匹配的,则显示null.

左/右外连接查询的结果  =  主表中有的而从表中没有的部分  +  内连接查询的结果.

左外连接和右外连接交换一下表的顺序可以实现相同的效果.

全外连接查询的结果  =  表A中有而表B中没有的部分  +  表B中有而表A中没有的部分  +  内连接查询的结果.