mysql常用四种连接_MySQL四种连接查询

内连接 交集

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);