Understanding SQL Joins: Left Join, Right Join, and Union Explained

SQL joins are powerful tools used to combine rows from two or more tables based on a related column between them.

In this blog post, we will explore the concepts of left join, right join, and union, and how they can be utilized in your SQL queries.

By the end of this article, you’ll have a clear understanding of how these joins work and when to use them.

Left Join

A left join returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Example:

Consider two tables: A and D. When we perform a left join on these tables based on the country column, we include all rows from the A table, even if there is no corresponding row in the D table.

If we just scan the two tables, we can understand that all the values in Country column of Table A is present in Table B except Antigua and Barbuda, Belgium. Correspondingly, the value for D column in those rows will be null when we perform the left join operation.

The result of the left join operation will be:

SQL Query for this operation would be:

select A.country, A, D 
from A Left Join D 
on A.Country=D.Country        

Right Join

A right join returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

Example:

Using the same A and D tables, a right join will include all rows from the D table, regardless of whether there is a matching row in the A table.

Now, let us again scan these tables but this time from the perspective of the right table. Looking closely, we can observe that apart from Chile and Costa Rica, all other values in Country column of table D is present in Table A.

Correspondingly, all the values for A column will be printed as it is from the left table but for Chile and Costa Rica, A value will be Null.

The result of Right Join Operation would be:

SQL Query for the Right Join Operation:

select D.country, A, D 
from A Right Join D 
on A.Country=D.Country        

Full Outer Join

Full Outer Join is not a function in MySQL, however we can logically do a full outer join using Union Operation.

As we know, Union combines the result set of two or more select statements and removes duplicate rows between the various select statements.

Hence, we can use Union operation to get the Full Outer Join by the following process:

1. Left Join Table 1 and Table 2
2. Right Join Table 1 and Table 2
3. Union between the resultant tables of Step 1 and Step 2

Example:

Suppose we want to print matched rows from both Table A and Table D, and unmatched rows from both the tables, we can perform Union between the two resultant tables from left join and right join operation.

The resultant table will have 12 Rows including 8 matched rows from both the table, Antigua & Barbuda, Belgium from Left Join Table, and Chile and Costa Rica from the right join table.

SQL Query for the operation:

select A.country, A, D 
from A Left Join D 
on A.Country=D.Country

Union 

select D.country, A, D 
from A Right Join D 
on A.Country=D.Country        

Union All

Union All performs similar to Union but it does not remove duplicates rather keep them as they are.

Example:

For the above two tables if we do Union All instead of Union, the result would be something like the following:

SQL Query for the Operation would be:

select A.country, A, D 
from A Left Join D 
on A.Country=D.Country

Union All

select D.country, A, D 
from A Right Join D 
on A.Country=D.Country        

SQL joins are powerful tools for merging data from different tables. Understanding left join, right join, and union operations will enhance your ability to write complex queries and manage your database more effectively. Practice these joins to become proficient in SQL.

Do let me know in the comments below if you liked my effort.

Also, if you want to understand it through a video explaination, you can go through the below link: