SQL的四种连接查询

SQL的四种连接查询

内连接：inner join 或者 join

-- PERSON表
create table person(
	id int, 
    name varchar(20),
    cardId int
);
-- card表
create table card(
	id int,
    name varchar(20)
);
-- 插入数据
insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'招商卡');

insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

-- inner join 内连接查询
select * from person inner join card on person.cardID = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+
-- 这个结果等于
select * from person, card where person.cardID = card.id;
-- 内联查询就是通过某个字段相等，查询出相关记录数据

外连接:

左连接 left join 或者 left outer join

select * from person left join card on person.cardID = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
+------+------+--------+------+--------+

左外连接会把左边表里面的数据取出来，对右边的表如果ON之后的条件满足就输出，不满足则为NULL

右连接 right join 或者 right outer join

right join（右外连接）：类似的，和左外连接相反

完全外连接 full join 或者 full outer join

select * from person full join card on person.cardID = card.id;
-- mysql 不支持！！！！要实现使用UNION把左右连接合在一起去
select * from person left join card on person.cardID = card.id
UNION
select * from person right join card on person.cardID = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 招商卡 |
+------+------+--------+------+--------+