SQL LEFT JOIN

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