Monday, 11 February 2013

SQL Server LEFT Outer JOIN Syntax and Example

The LEFT JOIN returns all rows from the Left Table (Table_I), includes the matching rows from the Right Table (Table_2).
In other words it returns all the matching rows from both the tables and non matching rows from left table.

SELECT Column_name(s) FROM Table_1 T1 LEFT JOIN Table_2 T2
ON  T1.Column_name=T2.Column_name

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

Example :

Suppose we have two tables as follows
The "ORDERS" Table:

Now we want to list all the Orders and the corresponding Customers details.

The "CUSTOMERS" Table:

We use the following Left Join Query :

Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,O.Sales_Period,O.Sales from ORDERS O LEFT JOIN CUSTOMERS C ON C.Cust_Id = O.Cust_Id

Output :

Left  Join with Non Match in Right Table :
Left outer join produces a complete set of records from Left Table, with the matching records (where available) in Right Table. If there is no match, the right side will contain null.

The following Left Join Query gives the results only from Left Table that do not have Match in Right Table :

Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,O.Sales_Period,O.Sales from ORDERS LEFT JOIN CUSTOMERS ON C.Cust_Id = O.Cust_Id  WHERE C.Cust_Id IS Null

Output :

Thanks, TAMATAM ; Business Intelligence Professional

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts