SQL学习：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 namesEmployees:
Employee_IDName01Hansen, Ola02Svendson, Tove03Svendson, Stephen04Pettersen, KariOrders:
Prod_IDProductEmployee_ID234Printer01657Table03865Chair03
Referring to Two TablesWe can select data from two tables by referring to two tables, like this:
ExampleWho has ordered a product, and what did they order?
SELECT Employees.Name, Orders.ProductFROM Employees, OrdersWHERE Employees.Employee_ID=Orders.Employee_IDResult
NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChairExampleWho ordered a printer?
SELECT Employees.NameFROM Employees, OrdersWHERE Employees.Employee_ID=Orders.Employee_IDAND Orders.Product='Printer'Result
NameHansen, Ola
Using JoinsOR we can select data from two tables with the JOIN keyword, like this:
Example INNER JOINSyntax
SELECT field1, field2, field3FROM first_tableINNER JOIN second_tableON first_table.keyfield = second_table.foreign_keyfieldWho has ordered a product, and what did they order?
SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDThe 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
NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChairExample LEFT JOINSyntax
SELECT field1, field2, field3FROM first_tableLEFT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfieldList all employees, and their orders - if any.
SELECT Employees.Name, Orders.ProductFROM EmployeesLEFT JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDThe 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
NameProductHansen, OlaPrinterSvendson, ToveSvendson, StephenTableSvendson, StephenChairPettersen, KariExample RIGHT JOINSyntax
SELECT field1, field2, field3FROM first_tableRIGHT JOIN second_tableON first_table.keyfield = second_table.foreign_keyfieldList all orders, and who has ordered - if any.
SELECT Employees.Name, Orders.ProductFROM EmployeesRIGHT JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDThe 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
NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChairExampleWho ordered a printer?
SELECT Employees.NameFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_IDWHERE Orders.Product = 'Printer'Result
NameHansen, Ola
页:
[1]