Join and Union is usually used in SQL queries to combine 2 or more data sources. I wrote one blog before to summarize all types of join. If you haven't read it yet, you can go ahead and read it here. In that blog, I divide types of join into 4 groups: Outer Join, Exclusive Join, Cross Join, and Self Join. However, I didn't share how to write SQL queries for each case.
In this blog, I will list 4 types of join (Left, Right, Inner, Full Outer) that are usually at the top and the other types at the bottom. For each type of join, I am going to share:
- How to write the SQL queries
- Example and explain
- Some used cases applied that type of join
If you are ready, let's get started!
Left Join (or Left Outer Join)
The result of the left join includes all values in the left table and the common values of both tables (Fig.1).
In the example above:
- Table A includes 5 unique IDs for 5 products
- Table B includes only 4 unique IDs (1, 2, 4, and 6), and the price value for each ID
In this case, table A is our primary table. If applying the LEFT JOIN to combine 2 tables, all values in table A will be kept, and the common IDs (which are ID 1, 2, and 4) between 2 tables.
ID numbers 3 and 5 don't exist in Table B and our primary table for Left Join is A, so those rows return NULL.
Use Cases
Left Join is usually used to combine more tables to get more data.
It can also be used to fill in the missing data from other tables but don't want to get all data from other tables.
Right Join (or Right Outer Join)
Opposite to the Left Join, the Right Join result includes all values in the right table and the common values of both tables.
Same table A and table B. If applying Right Join, the result will return all values from Table B and the common values between both tables based on the join key ID.
In the example above, table B will be the primary table, so all rows in Table B will be returned. ID number 6 doesn't exist in Table A, so it will return Null at ID = 6.
Use cases
Similar to the Left Join, the Right Join is used to combine data sources or to fill in the missing data from other tables but only wants to get data related to the primary table.
JOIN (or Inner Join)
Depending on the Relational Database Management System (RDBMS), you can type JOIN or INNER JOIN to apply the inner join 2 tables.
The result of the Inner Join is only the common values between 2 tables.
In the example above, by doing an inner join for 2 tables with the join key ID, the result only returns the common values between 2 tables. ID 1, 2, 4 in Table A matched with ID 1, 2, 4 in Table B.
Use cases
The Inner Join is used to get more data for the current table and only get the related data of the current table.
Full Outer Join (or Full Join)
Depending on the RDBMS, it could be FULL JOIN or FULL OUTER JOIN. The result of using Full outer join for 2 tables is all values of both tables.
As a result of the example above, all values appear in the result table. If the join key doesn't match, it will return Null.
Use cases
When you need to retrieve all values (including not matching values) from 2 or more tables.
Note: If all Null rows are removed, the result will be the same as the Inner Join -> Can use the Where clause to do that.
We usually use those 4 popular types of join in SQL queries. I hope it's useful to help you review. Based on those 4 types of join, other types of join are extended from those. I continue to share about all Exclusive Joins, Cross Join, and Self Join.
Left Exclusive Join
SQL doesn't have an Exclusive join keyword, so you need to do it from the LEFT JOIN.
The result of the Left Exclusive Join is values from the left table that the join key doesn't exist in the right table (which does not include the common values between the 2 tables).
The SQL query is almost the same as the Left Join example. However, you need to add the WHERE clause to filter that only gets the join key is Null in the RIGHT table.
For example, b.ID is the right join key from Table B. ID numbers 3 and 5 don't exist in Table B. To get the result of the Left Exclusive Join, you need to modify the Where clause that b.ID IS NULL.
There are many ways to write the Left Exclusive Join. You can also use NOT EXIST or NOT IN or !=ALL to write the Left Exclusive Join. I can rewrite the SQL query as:
SELECT *
FROM Table A as a
WHERE ID NOT EXISTS (
SELECT 1
FROM Table B as b
WHERE a.ID = b.ID
);
Or you can also use NOT IN
SELECT *
FROM Table A
WHERE ID NOT IN (
SELECT ID
FROM Table B
)
Or you can use !=ALL
SELECT *
FROM Table A
WHERE ID != ALL (
SELECT ID
FROM Table B
)
Use Cases
The Left Exclusive Join is usually used to check which values are in the left table but not in the right table. For example, in the real world, there are 2 grocery store datasets. We want to check which products are selling in the store A but the store B is not selling those.
Right Exclusive Join
Opposite to the Left Exclusive Join, the Right Exclusive Join only returns the value on the right table and does not include the common values between the 2 tables.
The Right Exclusive Join uses the Right Join keyword and includes the WHERE clause which the left join key is Null.
In the example above, only ID number 6 doesn't exist in Table A, so the result is only one row.
Similar to the Left Exclusive Join, there are many alternative ways to write the Right Exclusive Join.
Use Cases
Similar to the Left Exclusive Join, the Right Exclusive Join is used to return values that exist in the right table but do not exist in the left table.
Full Outer Exclusive Join
In Full Outer Join, we get all the values of the join tables. In Full Outer Exclusive Join, we also get values from both tables but exclude the common values between the 2 tables.
The Full Outer Exclusive Join also uses the FULL OUTER JOIN keyword but includes the WHERE clause to retrieve the data where the join key in the left and right table is NULL.
An alternative way to write SQL query for the Full Outer Exclusive Join is by using UNION ALL the Left Exclusive and the Right Exclusive Join.
Use Cases
When migrating or integrating data from multiple sources, you want to check if all records have been migrated correctly.
Or synchronize data between 2 systems, the Full Outer Exclusive Join can identify discrepancies to maintain data integrity. For a simple example, you synchronize the music from the computer to the phone. You would like to ensure all music has been synchronized.
Or in the analysis, you can check which products were sold last year and which products are not sold in this year.
Cross Join (Or Cartesian Join)
The Cross Join or Cartesian Join helps to combine 2 or more tables without the join key. The result is each row in the left table pairs with each row in the right table.
Not similar to other types of join, the Cross Join doesn't have the join key. From the left table, we use CROSS JOIN to the right table. The number of rows for the result equals the number of rows in the left table multiplied by the number of rows in the right table.
Use Cases
When you need to create all possible pairs of 2 tables, you can apply Cross Join. For example, I have a list of positions and a list of all the ways to reach the destination. I want to find the shortest way from all positions to the destination.
Self Join
Not similar to other types of join when there are 2 or more tables to join. The Self Join will join by itself.
In the example above, there is only 1 Table A with 3 columns: ID, Employee, and Manager_ID. For example: ID=1, Employee Hang isn't assigned a manager. ID=2, Connie is assigned with Manager_ID=1 (which is Hang with ID=1). This is a hierarchical problem.
To find the name of the Manager for each ID, you can do Self Join (join Table A by itself). Imagine that you are joining 2 same tables, the Manager_ID in the first table will be the ID in the second table.
Use Cases
The Self Join is usually used in the hierarchical problem or finding the related records. For example: from the example above, I can find employees have the same manager.
In this blog, I shared 9 different types of join and their use cases. I hope this blog is useful to you in understanding the differences between SQL join types and how to apply SQL queries to join tables in each case.
I hope to hear feedback from you. Thank you for reading!