4.七种连接方式

数据准备

CREATE DATABASE jointest;

CREATE TABLE tbl_dept(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30) DEFAULT NULL ,
  `locAdd` VARCHAR(40) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE =INNODB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL ,
  `deptId` INT(11) DEFAULT NULL ,
  PRIMARY KEY (`id`),
  KEY `fk_dept_id`(`deptId`)
) ENGINE = INNODB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;


INSERT INTO tbl_dept(deptName, locAdd) VALUES ('RD',11);
INSERT INTO tbl_dept(deptName, locAdd) VALUES ('HR',12);
INSERT INTO tbl_dept(deptName, locAdd) VALUES ('MK',13);
INSERT INTO tbl_dept(deptName, locAdd) VALUES ('MIS',14);
INSERT INTO tbl_dept(deptName, locAdd) VALUES ('FD',15);


INSERT INTO tbl_emp(NAME,deptId)VALUES ('z3', 1);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('z4', 1);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('z5', 1);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('w5', 2);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('w6', 2);

INSERT INTO tbl_emp(NAME,deptId)VALUES ('s7', 3);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('s8', 4);
INSERT INTO tbl_emp(NAME,deptId)VALUES ('s9', 51);

联接查询语法

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>

笛卡尔积

mysql> select * from  tbl_dept,tbl_emp;
+----+----------+--------+----+------+--------+
| id | deptName | locAdd | id | name | deptId |
+----+----------+--------+----+------+--------+
|  1 | RD       | 11     |  1 | z3   |      1 |
|  2 | HR       | 12     |  1 | z3   |      1 |
|  3 | MK       | 13     |  1 | z3   |      1 |
|  4 | MIS      | 14     |  1 | z3   |      1 |
|  5 | FD       | 15     |  1 | z3   |      1 |
|  1 | RD       | 11     |  2 | z4   |      1 |
|  2 | HR       | 12     |  2 | z4   |      1 |
|  3 | MK       | 13     |  2 | z4   |      1 |
|  4 | MIS      | 14     |  2 | z4   |      1 |
|  5 | FD       | 15     |  2 | z4   |      1 |
|  1 | RD       | 11     |  3 | z5   |      1 |
|  2 | HR       | 12     |  3 | z5   |      1 |
|  3 | MK       | 13     |  3 | z5   |      1 |
|  4 | MIS      | 14     |  3 | z5   |      1 |
|  5 | FD       | 15     |  3 | z5   |      1 |
|  1 | RD       | 11     |  4 | w5   |      2 |
|  2 | HR       | 12     |  4 | w5   |      2 |
|  3 | MK       | 13     |  4 | w5   |      2 |
|  4 | MIS      | 14     |  4 | w5   |      2 |
|  5 | FD       | 15     |  4 | w5   |      2 |
|  1 | RD       | 11     |  5 | w6   |      2 |
|  2 | HR       | 12     |  5 | w6   |      2 |
|  3 | MK       | 13     |  5 | w6   |      2 |
|  4 | MIS      | 14     |  5 | w6   |      2 |
|  5 | FD       | 15     |  5 | w6   |      2 |
|  1 | RD       | 11     |  6 | s7   |      3 |
|  2 | HR       | 12     |  6 | s7   |      3 |
|  3 | MK       | 13     |  6 | s7   |      3 |
|  4 | MIS      | 14     |  6 | s7   |      3 |
|  5 | FD       | 15     |  6 | s7   |      3 |
|  1 | RD       | 11     |  7 | s8   |      4 |
|  2 | HR       | 12     |  7 | s8   |      4 |
|  3 | MK       | 13     |  7 | s8   |      4 |
|  4 | MIS      | 14     |  7 | s8   |      4 |
|  5 | FD       | 15     |  7 | s8   |      4 |
|  1 | RD       | 11     |  8 | s9   |      5 |
|  2 | HR       | 12     |  8 | s9   |      5 |
|  3 | MK       | 13     |  8 | s9   |      5 |
|  4 | MIS      | 14     |  8 | s9   |      5 |
|  5 | FD       | 15     |  8 | s9   |      5 |
+----+----------+--------+----+------+--------+
40 rows in set (0.00 sec)

inner join 内联接

mysql> select * from tbl_emp a inner join tbl_dept b on a.deptid = b.id;
+----+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+

left join 左联接

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

right join 右联接

mysql> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id;
+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)


左表独有的数据

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)


右表独有的数据

mysql> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id where a.id is null;
+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)


全连接

mysql不支持 full outer join语法

但可以通过union实现

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id
    -> union 
    -> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id;
+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.01 sec)


左右表各自独有的

mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id where b.id is null
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id where a.id is null;
+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

多表连接查询

例: 四表连接

思路:子查询两两连接, 然后再两丙连接子查询

select * from
  (select city.name as city_name, region.id as region_id, region.name as region_name from city_info city left join region_info region on city.id = region.city_id) a
left join
  (select  district.name as district_name, district.region_id as region_id,  mgr.uid from district_tag district left join manager_district_relation mgr on district.id = mgr.district_tag_id) b
  on a.region_id = b.region_id;