sql join

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

OrderIDCustomerIDOrderDate1030821996-09-1810309371996-09-1910310771996-09-20

Then, look at a selection from the "Customers" table:

CustomerIDCustomerNameContactNameCountry1Alfreds FutterkisteMaria AndersGermany2Ana Trujillo Emparedados y heladosAna TrujilloMexico3Antonio Moreno TaqueríaAntonio MorenoMexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; Try it Yourself »

and it will produce something like this:

OrderIDCustomerNameOrderDate10308Ana Trujillo Emparedados y helados9/18/199610365Antonio Moreno Taquería11/27/199610383Around the Horn12/16/199610355Around the Horn11/15/199610278Berglunds snabbköp8/12/1996

---

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tablesLEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right tableRIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left tableFULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

   

 

转载于:https://www.cnblogs.com/oxspirt/p/11545955.html