mysql 左连接与右连接的区别吗_左连接，右连接，全外连接的区别是什么？

左连接右连接全连接的区别

These code has been tested for Mysql.

这些代码在Mysql上测试过。

Mysql does not support FULL OUTER JOIN.

Mysql不支持全外连接。

Create some tables for test / 作一些用于测试的表

Create two tables:

作两个表：

CREATE DATABASE test;

USE test;

CREATE TABLE t1(id int primary key auto_increment, name char(32));

CREATE TABLE t2(id int primary key auto_increment, name char(32));

Insert some data to both t1 and t2, they have same 'id':

向t1和t2插入一些数据，它们有一样的id:

INSERT INTO t1(id, name) VALUES(1, 'a'),(2,'b'),(3,'c');

INSERT INTO t2(id, age) VALUES(1, 11),(2,12),(3,13);

Insert some data to both t1 and t2, they have different 'id':

向t1和t2插入一些数据，它们有不同的id:

INSERT INTO t1(id, name) VALUES(5, 'e'),(7, 'g');

INSERT INTO t2(id, age) VALUES(4, 14), (6, 16);

Now table t1 has these data:

t1表有这些数据

+------+------+

| id | name |

+------+------+

| 1 | a |

| 2 | b |

| 3 | c |

| 5 | e |

| 7 | g |

+------+------+

table t2 has these data:

t2表有这些数据：

+------+------+

| id | age |

+------+------+

| 1 | 11 |

| 2 | 12 |

| 3 | 13 |

| 4 | 14 |

| 6 | 16 |

+------+------+

What is left join？什么是左连接？

SELECT t1.name,t2.age FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

+------+------+

| name | age |

+------+------+

| a | 11 |

| b | 12 |

| c | 13 |

| e | NULL |

| g | NULL |

+------+------+

We can see that it selects out all 'name' within table t1, no matter whether are matched rows in table t2, if there are no matched rows in table t2, the 'age' column will be NULl.

可见左连接选出了t1的所有数据，不论t2有没有对应的数据，如果t2没有对应的数据，则age那一列显示为空。

What is right join? / 什么是右连接？

SELECT t1.name,t2.age FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

+------+------+

| name | age |

+------+------+

| a | 11 |

| b | 12 |

| c | 13 |

| NULL | 14 |

| NULL | 16 |

+------+------+

We can see that it selects out all 'age' within table t2, no matter whether are matched rows in table t1, if there are no matched rows in table t1, the 'name' column will be NULl.

可见右连接选出了t2的所有数据，不论t1有没有对应的数据，如果t1没有对应的数据，则name那一列显示为NULL.

What is inner join? / 什么是内连接?

SELECT t1.name,t2.age FROM t1 INNER JOIN t2 ON t1.id = t2.id;

+------+------+

| name | age |

+------+------+

| a | 11 |

| b | 12 |

| c | 13 |

+------+------+

We can see that it selects out all rows that both in table t1 and t2.

可见内连接选出了在t1和t2都存在的数据。