The LEFT JOIN or LEFT OUTER JOIN is used to retrieve all the records from the Left table (Table A) if there is no match with the Right table (Table B). If there is no match in the right table then the result will have NULL representing the column for the right table. If any value matches with the left table so that particular record will be present in the result.
- Syntax of LEFT JOIN –
SELECT column1,column2,column3,...,columnN FROM TableA
LEFT JOIN
TableB
ON TableA.column_name = TableB.column_name ;
Example – Consider these two tables the Product table and the Manager table.
Demo Table –
Product Table
| Category | Region | City | Country | Customer_ID | Customer_name |
| Office Supplies | Central | Chicago | United States | SM-20950 | Suzanne McNair |
| Technology | East | New York City | United States | AH-10465 | Amy Hunt |
| Office Supplies | East | New York City | United States | AH-10465 | Amy Hunt |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan |
| Office Supplies | East | Dover | United States | EP-13915 | Emily Phan |
| Technology | West | Aurora | United States | TP-21565 | Tracy Poddar |
| Office Supplies | West | Aurora | United States | TP-21565 | Tracy Poddar |
| Office Supplies | East | Long Beach | United States | AR-10825 | Anthony Rawles |
| Office Supplies | West | Pasadena | United States | HA-14920 | Helen Andreada |
| Furniture | West | Pasadena | United States | HA-14920 | Helen Andreada |
Manager Table
| Region | Regional Manager |
| West | Sadie Pawthorne |
| East | Chuck Magee |
| Central | Roxanne Rodriguez |
| South | Fred Suzuki |
Now the below query will return all the records from the Product table even if there is no match in the Manager’s table.
SELECT
A.Category,A.City,A.[Customer Name],B.Region,B.[Regional Manager]
FROM [dbo].[Products] AS A
LEFT JOIN
[dbo].[Manager] AS B
ON A.Region = B.Region;
This output is the small section of the output by executing the above query –
| Category | City | Customer_ID | Region | Regional Manager |
| Office Supplies | Chicago | Suzanne McNair | Central | Roxanne Rodriguez |
| Technology | New York City | Amy Hunt | East | Chuck Magee |
| Office Supplies | New York City | Amy Hunt | East | Chuck Magee |
| Office Supplies | Dover | Emily Phan | East | Chuck Magee |
| Office Supplies | Dover | Emily Phan | East | Chuck Magee |
| Technology | Aurora | Tracy Poddar | West | Sadie Pawthorne |
| Office Supplies | Aurora | Tracy Poddar | West | Sadie Pawthorne |
| Office Supplies | Long Beach | Anthony Rawles | East | Chuck Magee |
| Office Supplies | Pasadena | Helen Andreada | West | Sadie Pawthorne |
| Furniture | Pasadena | Helen Andreada | West | Sadie Pawthorne |
