内连接 交集
inner join 或者 join
select * from person join card on person.cardId = card.id;
+------+--------------------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------------------+--------+------+-----------+
| 1 | 法外狂徒张三 | 1 | 1 | 饭卡 |
| 2 | 青春有你坤坤 | 3 | 3 | 公交卡 |
| 3 | 说唱选手电鳗 | 6 | 6 | 水卡 |
+------+--------------------+--------+------+-----------+
通过某个字段相等,查询出相关记录
外链接
左外链接 左边
left join 或者 left outer join
select * from person left join card on person.cardId = card.id;
左边数据取出来,右边数据,如果有相等的,显示,否则补null
+------+--------------------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------------------+--------+------+-----------+
| 1 | 法外狂徒张三 | 1 | 1 | 饭卡 |
| 2 | 青春有你坤坤 | 3 | 3 | 公交卡 |
| 3 | 说唱选手电鳗 | 6 | 6 | 水卡 |
+------+--------------------+--------+------+-----------+
右外链接 右边
right join 或 right outer join
select * from person right join card on person.cardId = card.id;
右边数据取出来,左边数据,如果有相等的,显示,否则补null
+------+--------------------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------------------+--------+------+-----------+
| 1 | 法外狂徒张三 | 1 | 1 | 饭卡 |
| 2 | 青春有你坤坤 | 3 | 3 | 公交卡 |
| 3 | 说唱选手电鳗 | 6 | 6 | 水卡 |
| NULL | NULL | NULL | 2 | 电卡 |
| NULL | NULL | NULL | 4 | 门禁卡 |
| NULL | NULL | NULL | 5 | 上网卡 |
+------+--------------------+--------+------+-----------+
完全外链接 mysql 不支持 full join all
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 | 6 | 水卡 |
| NULL | NULL | NULL | 2 | 电卡 |
| NULL | NULL | NULL | 4 | 门禁卡 |
| NULL | NULL | NULL | 5 | 上网卡 |
+------+--------------------+--------+------+-----------+
create table person(
id int,
name varchar(20),
cardId int
);
create table card(
id int,
name varchar(20)
);
insert into card values(1,'饭卡'),(2,'电卡'),(3,'公交卡'),(4,'门禁卡'),(5,'上网卡'),(6,'水卡');
insert into person values(1,'法外狂徒张三',1),(2,'青春有你坤坤',3),(3,'说唱选手电鳗',6);