SQL四种连接查询
----内连接
inner join或者join
外链接
1、左连接 left join 或者 left outer join
2、右连接 right join 或者 right outer join
3、完全外链接 full join 或者full outer join
创建两个表;
creat database testjoin;
person 表
id,name,cardId
create table person(id int,name varchar(20),cardId int);
| id | name | cardId |
+------+----------+--------+
| 1 | zhangsan | 1 |
| 2 | lisi | 3 |
| 3 | wangwu | 6 |
+------+----------+--------+
card 表
id,name
create table card(id int,name varchar(20));
| id | name |
+------+-------------+
| 1 | fanka |
| 2 | pufaka |
| 3 | nonghangka |
| 4 | zhonghangka |
| 5 | gongshangka |
+------+-------------+
----并没有创建外键
----inner join查询
----内联查询,其实就是两张表中的数据,通过某个字段相对,查询出相关记录数据。
select * from person join card on person.cardId=card.id;
| id | name | cardId | id | name |
+------+----------+--------+------+------------+
| 1 | zhangsan | 1 | 1 | fanka |
| 2 | lisi | 3 | 3 | nonghangka |
+------+----------+--------+------+------------+
2、left join(左外连接)
mysql> select * from person left join
card on person.cardId=card.id;
+------+----------+--------+------+------------+
| id | name | cardId | id | name |
+------+----------+--------+------+------------+
| 1 | zhangsan | 1 | 1 | fanka |
| 2 | lisi | 3 | 3 | nonghangka |
| 3 | wangwu | 6 | NULL | NULL |
+------+----------+--------+------+------------+
----左外连接,会把左边表里面的所有数据取出来,而右边表中的数据,如果有相等的就显示出来,如果没有,就会补NULL。
3、right join (右外连接)
mysql> select * from person right
outer join card on person.cardId=
card.id;
+------+----------+--------+------+-------------+
| id | name | cardId | id | name |
+------+----------+--------+------+-------------+
| 1 | zhangsan | 1 | 1 | fanka |
| 2 | lisi | 3 | 3 | nonghangka |
| NULL | NULL | NULL | 2 | pufaka |
| NULL | NULL | NULL | 4 | zhonghangka |
| NULL | NULL | NULL | 5 | gongshangka |
+------+----------+--------+------+-------------+
----右外连接,会把右边表里面的所有数据取出来,而左边表中的数据,如果有相等的就显示出来,如果没有,就会补NULL。
4、full join(全外连接)
mysql> select * from person full join card on
person.cardId=card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on
clause'
因为mysql不支持 full join(全外连接)。
mysql> 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 | zhangsan | 1 | 1 | fanka |
| 2 | lisi | 3 | 3 | nonghangka |
| 3 | wangwu | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | pufaka |
| NULL | NULL | NULL | 4 | zhonghangka |
| NULL | NULL | NULL | 5 | gongshangka |
+------+----------+--------+------+-------------+