七种连接查询mysql
在使用数据库查询语句时，
单表的查询有时候不能满足项目的业务需求
在项目开发过程中，
有很多需求都是要涉及到多表的连接查询.
连接查询：也可以叫跨表查询，需要关联多个表进行查询
以下通过两表实例来详细介绍连接的使用方式.
所有操作基本department表和employee表
department表:
以下为建表语句与表数据
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`address` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
表数据:
INSERT INTO `department`
VALUES ('1', '研发部(RD)', '2层');
INSERT INTO `department`
VALUES ('2', '人事部(HR)', '3层');
INSERT INTO `department`
VALUES ('3', '市场部(MK)', '4层');
INSERT INTO `department`
VALUES ('4', '后勤部(MIS)', '5层');
INSERT INTO `department`
VALUES ('5', '财务部(FD)', '6层');
employee表:
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
表数据
INSERT INTO `employee`
VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee`
VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee`
VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee`
VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee`
VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee`
VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee`
VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee`
VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
一、内连接
图示:
作用:
查询两张表的共有部分
语句:
Select <select_list> from tableA A
Inner join tableB B
on A.Key = B.Key
原表数据:
示例:
SELECT * from employee e
INNER JOIN department d
on e.depart_id = d.id;
查询结果数据:
二、左连接
图示:
作用:
把左边表的内容全部查出,右边表只查出满足条件的记录
语句:
Select <select_list> from tableA A
Left Join tableB B
on A.Key = B.Key
原表数据:
示例:
SELECT * from employee e
LEFT JOIN department d
on e.depart_id = d.id;
查询结果数据:
三、右连接
图示:
作用:
把右边表的内容全部查出,左边表只查出满足条件的记录
语句:
Select <select_list> from tableA A
Left Join tableB B
on A.Key = B.Key
原表数据:
示例:
SELECT * from employee e
RIGHT JOIN department d
on e.depart_id = d.id;
查询结果数据:
四、查询左表独有数据
图示:
作用:
查询A的独有数据
语句:
Select <select_list> from tableA A
Left Join tableB B
on A.Key = B.Key where B.key IS NULL
原表数据:
示例:
SELECT * from employee e
LEFT JOIN department d
on e.depart_id = d.id WHERE d.id IS NULL;
查询结果数据:
五、查询右表独有数据
图示:
作用:
查询B的独有数据
语句:
Select <select_list> from tableA A
Right Join tableB B
on A.Key = B.Key where A.key IS NULL
原表数据:
示例:
SELECT * from employee e
RIGHT JOIN department d
on e.depart_id = d.id WHERE e.id IS NULL;
查询结果数据:
六、全连接
图示:
作用:
查询两个表的全部信息
语句:
Select <select_list> from tableA A
Full Outter Join tableB B on A.Key = B.Key
注:Mysql 默认不支持此种写法 Oracle支持
在Mysql中可以使用UNION来实现相应操作
原表数据:
示例:
SELECT * from employee e
LEFT JOIN department d
on e.depart_id = d.id
UNION
SELECT * from employee e
RIGHT JOIN department d
on e.depart_id = d.id
查询结果数据:
七、查询左右表各自的独有的数据
图示:
作用:
查询A和B各自的独有的数据
语句:
Select <select_list> from tableA A
Full Outter Join tableB B
on A.Key = B.Key where A.key = null or B.key=null
原表数据:
示例:
SELECT * from employee e
LEFT JOIN department d
on e.depart_id = d.id WHERE d.id is NULL
UNION
SELECT * from employee e
RIGHT JOIN department d
on e.depart_id = d.id
WHERE e.depart_id is NULL
查询结果数据:
