Learn From - Interactive SQL


Next Chapter »

SQL LEFT JOIN


LEFT JOIN is used to get all the records from the left table (table1) and matching records from the right table (table2).

SQL LEFT JOIN Syntax


SELECT column_name1, column_name2 ...
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name

Below is shown the "Customers" table:

CustomerID CustomerName EmailID Address City PostalCode Country
C001 Fredrick Johnson FJohnson@yahoo.com Berkeley Gardens 12 Brewery London WX1 6LT UK
C002 Mary Davidson Mary@gmail.com 35 King George Madrid 28023 Spain
C003 George Clooney George@hotmail.com Obere Str. 57 Berlin 12209 Germany
C004 David Louis David@mail.com 2743 Bering St. NewJersy 07019 USA
C005 Johnny Anderson Johnny@gmail.com 55 Grizzly Peak Rd. NewJersy 07019 USA
C006 Paul Henriot PaulHenriot@yahoo.com 59 rue de l'Abbaye Paris 51100 France
C007 Henry King Henry@mail.com 120 Hanover Sq. London WA1 1DP UK
C008 Robert John Robert@gmail.com 87 Polk St. Suite 5 San Francisco 94117 USA

In the Customers table, "CustomerID" is Primary Key with unique values. You can see that the "CustomerID" column values do not repeat.

Below is shown the "Orders" table:

OrderID CustomerID Date Amount
D001 C001 4/3/2007 12:00:00 AM 150.23
D002 C003 1/22/2005 12:00:00 AM 123.57
D003 C005 3/19/2003 12:00:00 AM 220.43
D004 C007 7/21/2008 12:00:00 AM 157.72
D005 C008 2/28/2009 12:00:00 AM 223.78
D006 C011 3/21/2005 12:00:00 AM 245.34

In the Orders table, the "OrderID" is the Primary Key. Here you can see that the "CustomerID" column is the Foreign Key.

The Customers and the Orders table are related to each other using "CustomerID" column.

SQL Left JOIN Example


To retrieve all the data from Customers table and matching records from Orders table.

Text Editor below has the SQL Query, click the "View Result" button, to see the result.

Select Customers.CustomerID, Customers.CustomerName, Customers.EmailID, Orders.OrderID, Orders.Date, Orders.Amount
From Customers
Left JOIN Orders on Customers.CustomerID = Orders.CustomerID
Order By Orders.OrderID desc

Note: Order By clause is used to sort in descending order using OrderID, so that you can easily see the NULL values in the executed resultset.

SQL in Text Editor:








Next Chapter »