MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

最新推荐文章于 2024-01-19 10:51:31 发布

翻译最新推荐文章于 2024-01-19 10:51:31 发布 · 1.1k 阅读

文章标签：

#join #mysql #null #360 #数据库

数据库专栏收录该内容

0 篇文章

订阅专栏

本文通过实例解释了数据库中JOIN的不同类型，包括INNER JOIN、LEFT JOIN和RIGHT JOIN的区别及应用场景。

--网上找到一篇介绍join的很简单，和大家分享
在数据库中，数据是被分别放在不同的表里面的，通过SELECT 语句把不同表里面的数据连接在一起，按照需要的条件生成输出。大家经常搞不懂join这个语法，所以接下来就详细的进行说明。

首先是一些样例数据，表一demo_people
Mr Brown, Person number 1, has a phone number 01225 708225
Miss Smith, Person number 2, has a phone number 01225 899360
Mr Pullen, Person number 3, has a phone number 01380 724040

表2 demo_property
Person number 1 is selling property number 1 - Old House Farm
Person number 3 is selling property number 2 - The Willows
Person number 3 is (also) selling property number 3 - Tall Trees
Person number 3 is (also) selling property number 4 - The Melksham Florist
Person number 4 is selling property number 5 - Dun Roamin.

mysql> select * from demo_people;

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

| name       | phone        | pid |

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

| Mr Brown   | 01225 708225 |    1 |

| Miss Smith | 01225 899360 |    2 |

| Mr Pullen | 01380 724040 |    3 |

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

3 rows in set (0.00 sec)

mysql> select * from demo_property;

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

| pid | spid | selling              |

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

|    1 |    1 | Old House Farm       |

|    3 |    2 | The Willows          |

|    3 |    3 | Tall Trees           |

|    3 |    4 | The Melksham Florist |

|    4 |    5 | Dun Roamin           |

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

5 rows in set (0.00 sec)

mysql>

如果用一般的JOIN（不用INNER, OUTER, LEFT or RIGHT关键字中的任何一个），将从两张表中得对符合条件的结果。其他不符合条件的记录将不能输出。

mysql> select name, phone, selling

from demo_people join demo_property
on demo_people.pid = demo_property.pid;

如果用LEFT JOIN，将得到和上面一般JOIN一样的结果，并且从左边的表中得到一条额外的记录。也就是说左连接保证了左边的表中所有的人都被提及。

mysql> select name, phone, selling 

from demo_people left join demo_property 

on demo_people.pid = demo_property.pid; 

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

| name       | phone        | selling              |

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

| Mr Brown   | 01225 708225 | Old House Farm       |

| Miss Smith | 01225 899360 | NULL                 |

| Mr Pullen  | 01380 724040 | The Willows          |

| Mr Pullen  | 01380 724040 | Tall Trees           |

| Mr Pullen  | 01380 724040 | The Melksham Florist |

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

5 rows in set (0.00 sec)



mysql>

如果用RIGHT JOIN，将得到和上面一般JOIN一样的结果，并且从右边的表中得到一条额外的记录。在这个例子中表示虽然没有指定的卖家，但是所有的商品都将显示出来



mysql> select name, phone, selling 

from demo_people right join demo_property 

on demo_people.pid = demo_property.pid;

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

| name      | phone        | selling              |

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

| Mr Brown  | 01225 708225 | Old House Farm       |

| Mr Pullen | 01380 724040 | The Willows          |

| Mr Pullen | 01380 724040 | Tall Trees           |

| Mr Pullen | 01380 724040 | The Melksham Florist |

| NULL      | NULL         | Dun Roamin           |

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

5 rows in set (0.00 sec)



mysql>

An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities.

INNER JOIN 和 full join,功能一样, OUTER JOIN 放在LEFT or RIGHT 后面。结果不变

原文：http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html