join联接查询--SQL中 inner join、 left join 、right join、 outer join之间的区别

SQL中 inner join、 left join、right join、 outer join之间的区别

left join=left outer join  right join=right outer join

cross join=outer join=full jion

inner join= join

 

A表(a1,b1,c1)     B表(a2,b2)
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90      02   李四
03   英语 80      04   王五

 

select A.*,B.* from A
inner join B on(A.a1=B.a2)
结果是：
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90      02   李四

 

select A.*,B.* from A
cross join B on(A.a1=B.a2)
结果是：
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90       02   李四


select A.*,B.* from A
left outer join B on(A.a1=B.a2)
结果是:
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90      02   李四
03   英语 80      NULL  NULL

select A.*,B.* from A
right outer join B on(A.a1=B.a2)
结果是:
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90      02   李四
NULL NULL NULL     04    王五

select A.*,B.* from A
full outer join B on(A.a1=B.a2)
结果是:
a1   b1  c1       a2    b2
01   数学 95      01   张三
02   语文 90      02   李四
03   英语 80      NULL  NULL
NULL NULL NULL     04    王五

 

CROSS JOIN ，inner join ，join等， 都是笛卡尔积 如果不带WHERE，ON条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql> describe authors;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| author_id   | int(11)  | NO   | PRI |         |       |
| author_name | char(8)  | NO   |     |         |       |
| adress      | char(50) | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into book values("1","白鹿原","01");
Query OK, 1 row affected (0.08 sec)

mysql> selet * from book;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selet
 * from book' at line 1
mysql> select * from book;
+---------+-----------+-----------+
| book_id | book_name | author_id |
+---------+-----------+-----------+
|       1 | 白鹿原    | 01        |
+---------+-----------+-----------+
1 row in set (0.04 sec)

mysql> insert into book values("2","bailuyuan2","01");
ERROR 1406 (22001): Data too long for column 'book_name' at row 1
mysql> insert into book values("2","白鹿书院","01");
Query OK, 1 row affected (0.00 sec)

mysql> insert into book values("3","红高粱","02");
Query OK, 1 row affected (0.00 sec)

mysql> insert into book values("4","红磨坊","02");
Query OK, 1 row affected (0.00 sec)

mysql> insert into book values("5","黑土地","03");
Query OK, 1 row affected (0.00 sec)

mysql> insert into book values("6","黑金","03");
Query OK, 1 row affected (0.05 sec)

mysql> insert into authors values("01","陈忠实","陕西");
Query OK, 1 row affected (0.00 sec)

mysql> insert into authors values("02","莫言","北京");
Query OK, 1 row affected (0.00 sec)

mysql> insert into authors values("02","赵二","吉林");
ERROR 1062 (23000): Duplicate entry '2' for key 1
mysql> insert into authors values("03","赵二","吉林");
Query OK, 1 row affected (0.00 sec)

mysql> insert into authors values("04","王武","河南");
Query OK, 1 row affected (0.00 sec)

mysql> insert into book values("6","蓝天","05");
ERROR 1062 (23000): Duplicate entry '6' for key 1
mysql> insert into book values("7","蓝天","05");
Query OK, 1 row affected (0.00 sec)

mysql> select * from book;
+---------+-----------+-----------+
| book_id | book_name | author_id |
+---------+-----------+-----------+
|       1 | 白鹿原    | 01        |
|       2 | 白鹿书院  | 01        |
|       3 | 红高粱    | 02        |
|       4 | 红磨坊    | 02        |
|       5 | 黑土地    | 03        |
|       6 | 黑金      | 03        |
|       7 | 蓝天      | 05        |
+---------+-----------+-----------+
7 rows in set (0.09 sec)

mysql> select * from authors;
+-----------+-------------+--------+
| author_id | author_name | adress |
+-----------+-------------+--------+
|         1 | 陈忠实      | 陕西   |
|         2 | 莫言        | 北京   |
|         3 | 赵二        | 吉林   |
|         4 | 王武        | 河南   |
+-----------+-------------+--------+
4 rows in set (0.00 sec)

mysql> select authors.author_name,book.book_name from authors inner join book
    -> ;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 莫言        | 白鹿原    |
| 赵二        | 白鹿原    |
| 王武        | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 白鹿书院  |
| 赵二        | 白鹿书院  |
| 王武        | 白鹿书院  |
| 陈忠实      | 红高粱    |
| 莫言        | 红高粱    |
| 赵二        | 红高粱    |
| 王武        | 红高粱    |
| 陈忠实      | 红磨坊    |
| 莫言        | 红磨坊    |
| 赵二        | 红磨坊    |
| 王武        | 红磨坊    |
| 陈忠实      | 黑土地    |
| 莫言        | 黑土地    |
| 赵二        | 黑土地    |
| 王武        | 黑土地    |
| 陈忠实      | 黑金      |
| 莫言        | 黑金      |
| 赵二        | 黑金      |
| 王武        | 黑金      |
| 陈忠实      | 蓝天      |
| 莫言        | 蓝天      |
| 赵二        | 蓝天      |
| 王武        | 蓝天      |
+-------------+-----------+
28 rows in set (0.06 sec)

mysql> select authors.author_name,book.book_name from authors cross join book
    -> ;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 莫言        | 白鹿原    |
| 赵二        | 白鹿原    |
| 王武        | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 白鹿书院  |
| 赵二        | 白鹿书院  |
| 王武        | 白鹿书院  |
| 陈忠实      | 红高粱    |
| 莫言        | 红高粱    |
| 赵二        | 红高粱    |
| 王武        | 红高粱    |
| 陈忠实      | 红磨坊    |
| 莫言        | 红磨坊    |
| 赵二        | 红磨坊    |
| 王武        | 红磨坊    |
| 陈忠实      | 黑土地    |
| 莫言        | 黑土地    |
| 赵二        | 黑土地    |
| 王武        | 黑土地    |
| 陈忠实      | 黑金      |
| 莫言        | 黑金      |
| 赵二        | 黑金      |
| 王武        | 黑金      |
| 陈忠实      | 蓝天      |
| 莫言        | 蓝天      |
| 赵二        | 蓝天      |
| 王武        | 蓝天      |
+-------------+-----------+
28 rows in set (0.00 sec)

mysql> select authors.author_name,book.book_name from authors  join book
    -> ;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 莫言        | 白鹿原    |
| 赵二        | 白鹿原    |
| 王武        | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 白鹿书院  |
| 赵二        | 白鹿书院  |
| 王武        | 白鹿书院  |
| 陈忠实      | 红高粱    |
| 莫言        | 红高粱    |
| 赵二        | 红高粱    |
| 王武        | 红高粱    |
| 陈忠实      | 红磨坊    |
| 莫言        | 红磨坊    |
| 赵二        | 红磨坊    |
| 王武        | 红磨坊    |
| 陈忠实      | 黑土地    |
| 莫言        | 黑土地    |
| 赵二        | 黑土地    |
| 王武        | 黑土地    |
| 陈忠实      | 黑金      |
| 莫言        | 黑金      |
| 赵二        | 黑金      |
| 王武        | 黑金      |
| 陈忠实      | 蓝天      |
| 莫言        | 蓝天      |
| 赵二        | 蓝天      |
| 王武        | 蓝天      |
+-------------+-----------+
28 rows in set (0.00 sec)

mysql> select authors.author_name,book.book_name from authors  join book on
    -> authors.author_id=book.author_id;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 红高粱    |
| 莫言        | 红磨坊    |
| 赵二        | 黑土地    |
| 赵二        | 黑金      |
+-------------+-----------+
6 rows in set (0.40 sec)

mysql> select authors.author_name,book.book_name from authors outer join book on

    -> authors.author_id=book.author_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'outer
 join book on
authors.author_id=book.author_id' at line 1
mysql> select authors.author_name,book.book_name from authors  join book on
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1

mysql> select authors.author_name,book.book_name from authors left join book on
    -> authors.author_id=book.author_id;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 红高粱    |
| 莫言        | 红磨坊    |
| 赵二        | 黑土地    |
| 赵二        | 黑金      |
| 王武        | NULL      |
+-------------+-----------+
7 rows in set (0.04 sec)

mysql> select authors.author_name,book.book_name from authors right join book on
 authors.author_id=book.author_id;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 红高粱    |
| 莫言        | 红磨坊    |
| 赵二        | 黑土地    |
| 赵二        | 黑金      |
| NULL        | 蓝天      |
+-------------+-----------+
7 rows in set (0.00 sec)

mysql> select authors.author_name,book.book_name from authors cross join book on
 authors.author_id=book.author_id;
+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 红高粱    |
| 莫言        | 红磨坊    |
| 赵二        | 黑土地    |
| 赵二        | 黑金      |
+-------------+-----------+
6 rows in set (0.00 sec)

mysql> select authors.author_name,book.book_name from authors full join book on
authors.author_id=book.author_id;
ERROR 1054 (42S22): Unknown column 'authors.author_name' in 'field list'
mysql> select authors.author_name,book.book_name from authors full join book on
authors.author_id=book.author_id;

+-------------+-----------+
| author_name | book_name |
+-------------+-----------+
| 陈忠实      | 白鹿原    |
| 陈忠实      | 白鹿书院  |
| 莫言        | 红高粱    |
| 莫言        | 红磨坊    |
| 赵二        | 黑土地    |
| 赵二        | 黑金      |
+-------------+-----------+
6 rows in set (0.00 sec)