连接查询
连接查询分为:内连接查询和外连接查询
内连接查询:查询的结果集只返回满足条件的数据
外连接查询:查询的结果集除了返回满足条件的数据外,不满足条件的也返回,用
null
代替。
1
内连接
内连接分为:自然连接,交叉连接,
USING,ON
语法:
SELECT table1.column, table2.column
FROM
table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)];
(1)交叉连接
交叉连接也叫笛卡集,查询的结果是两个表的乘积
语法:
SELECT table1.column, table2.column
FROM
table1
CROSS JOIN table2;
select count(*) from dmhr.employee;--856
select count(*) from dmhr.department;--46
select count(*) from dmhr.employee cross join
dmhr.department;
等价于
select count(*) from dmhr.employee,dmhr.department;
(
2
)自然连接
自然连接不需要指定连接列,两个表的连接列需要列名和数据类型一致。
语法:
SELECT table1.column, table2.column
FROM
table1
NATURAL JOIN table2;
select
a.employee_name,b.department_name
from dmhr.employee a natural join dmhr.department b;
(
3
)
USING
在有多个连接列同时满足时,可以指定连接列
语法:
SELECT table1.column, table2.column
FROM
table1
JOIN table2 USING (column_name)
;
select
a.employee_name,department_id,b.department_name
from dmhr.employee a join dmhr.department b
using (department_id);
(
4
)
ON
语法:
SELECT table1.column, table2.column
FROM
table1
JOIN table2
ON(table1.column_name = table2.column_name);
select
a.employee_name,a.department_id,b.department_name
from dmhr.employee a join dmhr.department b
on a.department_id=b.department_id;
4.5.2
外连接
外连接分为:左外连接,右外连接,全外连接
语法:
SELECT table1.column, table2.column
FROM
table1
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name);
左外连接:
返回左边表的所有记录,右边表的记录只返回满足条件的,不满足的就用
null
代替。
语法:
SELECT table1.column, table2.column
FROM
table1
LEFT OUTER JOIN table2
ON (table1.column_name = table2.column_name);
select
a.employee_name,a.job_id,b.job_title
from dmhr.employee a left join dmhr.job b
on a.job_id=b.job_id;
右外连接:
返回右边表的所有记录,左边表的记录只返回满足条件的,不满足的就用
null
代替。
语法:
SELECT table1.column, table2.column
FROM
table1
RIGHT OUTER JOIN table2
ON (table1.column_name = table2.column_name);
select
a.employee_name,a.job_id,b.job_title
from dmhr.employee a right join dmhr.job b
on a.job_id=b.job_id;
全外连接:
左外连接
+
右外连接
语法:
SELECT table1.column, table2.column
FROM
table1
FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name);
select
a.employee_name,a.job_id,b.job_title
from dmhr.employee a full join dmhr.job b
on a.job_id=b.job_id;