SQL四种连接查询

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 |
+------+----------+--------+------+-------------+