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 |
