SQL LEFT OUTER JOIN

What is SQL LEFT OUTER JOIN

The SQL LEFT OUTER JOIN is the types of the outer join to combine the two tables. It combines the two table but prefer the rows of the first table and add all the rows from the first table to the resulted table.

SQL LEFT OUTER JOIN Image

To get the left join output using SQL, it finds all the rows from the first table including the matching rows from the right table. However, if there is no match in the second table it returns a null value.

How to Use LEFT OUTER JOIN in SQL

To use this types of the outer join of SQL, you have to use the two tables. The first table is the main table from where you have to take all the rows for the matching columns.

Syntax

Now, find all the values of the selected columns in the SQL query. It results out all the matching column rows from the first column and if there is no match with the second column, it returns the null value.

Example of SQL LEFT OUTER JOIN

Let’s create the two tables given below to understand the example of left outer join in SQL server.

Below are the two tables contain the column with one column matching rows.

SQL LEFT OUTER Join Example Using the Select Statement

The first table is Purchaser table and second is the Seller table. The first table contains the list of the purchaser tables
Table 1: Purchaser

Purchaser_ID Purchaser_Name Plot_No Service_Id
1 Sam 12 1001
2 Pill 13 1002
3 Don 14 1003
4 Brock 15 1004

The second table is the table contains the list of sellers.
Table 2: Seller

Id Seller_Name Seller_Email
1001 Big Show [email protected]
1002 Gem [email protected]
1003 Matt [email protected]

Now, let’s combine the above two tables using the example given below.

Example

Output

Service_Id Seller_Name Purchaser_Name Plot_No
1001 Big Show Sam 12
1002 Gem Pill 13
1003 Matt Don 14
1004 Null Brock 15

You must also read.

Reference
Join Syntax