SQL LEFT JOIN Keyword

« Previous Chapter Next Chapter »

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN Example

The "Persons" table:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans
2 Jacob Mary Los Angels - USA New Orleans
3 Maxwell Glen Australia Sydney

The "Purchases" table:

PU_ID OrderNo P_Id
1 I-1567 3
2 I-6008 3
3 I-1110 1
4 I-5616 1
5 I-9009 15

Now we want to list all the persons and their Purchases - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Purchases.OrderNo
FROM Persons
LEFT JOIN Purchases
ON Persons.P_Id=Purchases.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Karmen Kasa I-1110
Karmen Kasa I-5616
Maxwell Glen I-1567
Maxwell Glen I-6008
Jacob Mary  

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Purchases).


« Previous Chapter Next Chapter »

Have Any Suggestion? We Are Waiting To Hear from YOU!

Your Query was successfully sent!