Monday, October 19, 2015

SQL JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table:
OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

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

CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico

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, if we run the following SQL statement (that contains an INNER JOIN):

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID; 
it will produce something like this:
OrderID CustomerName OrderDate
10308 Ana Trujillo Emparedados y helados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
PS! INNER JOIN is the same as JOIN.


SQL INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;



CustomerNameOrderID
Ana Trujillo Emparedados y helados  10308 
Antonio Moreno Taquería  10365 
Around the Horn  10383 
Around the Horn  10355 
Berglunds snabbköp  10278 
Berglunds snabbköp  10280 
Berglunds snabbköp  10384 
Blondel père et fils  10265 
Blondel père et fils  10436 
Blondel père et fils  10297 
Blondel père et fils  10360 
Bólido Comidas preparadas  10326 
Bon app'  10340 
Bon app'  10362 
Bon app'  10331 
Bottom-Dollar Marketse  10411 
Bottom-Dollar Marketse  10431 
Bottom-Dollar Marketse  10389 
Bottom-Dollar Marketse  10410 
B's Beverages  10289 

SQL INNER JOIN