two articles about MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
来源:互联网 发布:远程协助软件排行 编辑:程序博客网 时间:2024/05/18 18:52
See also Search our site! and Well House Consultants home page
If this isn't quite the question you're looking to have answered, we've got a MySQL IAQ (Infrequently Answered Questions that may help you
First, some sample data:
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
and also:
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>
If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:
mysql> select name, phone, selling
from demo_people 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 |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)
mysql>
If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every PERSON gets a mention:
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>
If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join - im my example, that means that each property gets a mention even if we don't have seller details:
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 compatability and doesn't add an extra capabilities.
Extra link to three way join in solution centre (would have added that link in the comment except that it "spam trapped!
SQL JOIN
Joins and Keys
Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:
- The "Employee_ID" column is the primary key of the "Employees" table
- The "Prod_ID" column is the primary key of the "Orders" table
- The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names
Employees:
Orders:
Referring to Two Tables
We can select data from two tables by referring to two tables, like this:
Example
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.ProductFROM Employees, OrdersWHERE Employees.Employee_ID=Orders.Employee_ID
Result
Example
Who ordered a printer?
SELECT Employees.NameFROM Employees, OrdersWHERE Employees.Employee_ID=Orders.Employee_IDAND Orders.Product='Printer'
Result
Using Joins
OR we can select data from two tables with the JOIN keyword, like this:
Example INNER JOIN
Syntax
SELECT field1, field2, field3FROM first_tableINNER JOIN second_tableON first_table.keyfield = second_table.foreign_keyfield
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Result
Example LEFT JOIN
Syntax
SELECT field1, field2, field3FROM first_tableLEFT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfield
List all employees, and their orders - if any.
SELECT Employees.Name, Orders.ProductFROM EmployeesLEFT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Result
Example RIGHT JOIN
Syntax
SELECT field1, field2, field3FROM first_tableRIGHT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfield
List all orders, and who has ordered - if any.
SELECT Employees.Name, Orders.ProductFROM EmployeesRIGHT JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Result
Example
Who ordered a printer?
SELECT Employees.NameFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDWHERE Orders.Product = 'Printer'
Result
- two articles about MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
- MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
- JOIN用法 INNER JOIN/LEFT JOIN/RIGHT JOIN/OUTER JOIN
- INNER JOIN, LEFT JOIN , RIGHT JOIN, FULL JOIN, Update inner join, Delete inner join, on And
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- left join , right join , full outer join , inner join
- left join,right join,inner join,outer join的用法
- Inner join,outer join,left join,right join的区别
- Inner join,outer join,left join,right join的区别
- mysql left join right join inner join
- mysql left join,right join,inner join
- Mysql-left join,right join,inner join
- mysql left join,right join,inner join
- 【SQL】 left join、right join and inner join的区别
- cross join & natural join & inner join & left outer join & right outer join & full outer join
- inner join&left outer join&right outer join
- Youtube的个人视频门户:Portal:Youtube director
- XMB介绍
- 如何处理浮点数
- OpenLDAP学习笔记
- 学会和同事相处的30个原则
- two articles about MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
- VB6写的书籍下载软件 --老牛下书,还不错
- 微软开放Windows CE全部核心代码
- 日期转换
- 智能客户端的定义
- 定时器与中断概念
- 无题
- Hibernate中any元素的应用体会
- Jbpm中进程实例的挂起与释放