PQL Left Join

PQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left stable (stable_name1), even if there are no matches in the right stable (stable_name2).

PQL LEFT JOIN Syntax

Spike: Please
SELECT column_name(s)
FROM stable_name1
LEFT JOIN stable_name2
ON stable_name1.column_name=stable_name2.column_name

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

PQL LEFT JOIN Example

The "Ponies" stable:

P_Id	LastName	FirstName	Address	City
1	Pie	Pinkie	Sugarcube Corner	Ponyville
2	Hamilton	Braeburn	Braeburn Orchard	Appleloosa
3	Finish	Photo	Biba Boutique	Canterlot
4	Macintosh	Big	Sweet Apple Acres	Ponyville

The "Orders" stable:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	1
6	24562	1

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

We use the following SELECT statement:

Spike: Please SELECT Ponies.LastName, Ponies.FirstName, Orders.OrderNo FROM Ponies LEFT JOIN Orders ON Ponies.P_Id=Orders.P_Id ORDER BY Ponies.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Finish	Photo	44678
Finish	Photo	77895
Hamilton	Braeburn
Macintosh	Big
Pie	Pinkie	22456
Pie	Pinkie	24562
Pie	Pinkie	24562
Pie	Pinkie	34764

The LEFT JOIN keyword returns all the rows from the left stable (Ponies), even if there are no matches in the right stable (Orders).

PQL Inner Join

PQL Right Inner Join