Understanding SQL JOINS
Structured Query Language (SQL) allows us to perform operations on tables in a relational database, in order to access information. These operations include updating, creating, deleting, or selecting records in a table.
What is a join?
Join means to combine something; in the context of SQL, joining two or more tables together so we can obtain a collection of information that we can use in subsequent analysis.
Essentially, SQL join is a concept that allows us to combine and retrieve data from two or more tables, by joining the rows of the table, based on a related (common) column among those tables.
Different types of SQL Joins
- Inner Join
- Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
Putting it all into practice
Let's take two sample tables, which we will use in our examples below:
Inner Join
The INNER JOIN is used when we want matching data from both tables. The highlighted green represents the inner join between two tables.
In this example, where we have two tables T1 (wild_animals1) and T2 (wild_animals2), we want to get wild animals that are present in both tables. Visually, we can tell that ‘Cheetah’ and ‘Elephant’ are present on both the tables.
Let's see how to do this in SQL:
SELECT * FROM
wild_animals1
INNER JOIN wild_animals2
ON wild_animals1.Name = wild_animals2.Name
The Keyword ‘on’ is used to identify the basis of joining between two tables. In this case, both tables are joined based ON common wild animal names.
The resulting join table looks like this:
LEFT OUTER JOIN / LEFT JOIN
The LEFT OUTER JOIN is used when we want ALL data from the left table and matching data from the right table. The highlighted green represents the left outer join or left join between two tables.
In this example, where we have two tables T1 (wild_animals1) and T2 (wild_animals2), we want to get wild animals that are present in left table T1 and matching data from right table T2. If there is no matching data in the right table, it simply returns NULL.
Let's see how to do this in SQL:
SELECT * FROM
wild_animals1
LEFT JOIN wild_animals2
ON wild_animals1.Name = wild_animals2.Name
The resulting join table looks like this:
RIGHT OUTER JOIN / RIGHT JOIN
The RIGHT OUTER JOIN is used when we want ALL data from the right table and matching data from the left table. It's the opposite of LEFT JOIN. The highlighted green represents the right outer join or right join between two tables.
In this example, where we have two tables T1 (wild_animals1) and T2 (wild_animals2), we want to get wild animals that are present in right table T1 and matching data from left table T2. If there is no matching data in the left table, it simply returns NULL.
Let's see how to do this in SQL:
SELECT * FROM
wild_animals1
RIGHT JOIN wild_animals2
ON wild_animals1.Name = wild_animals2.Name
The resulting join table looks like this:
FULL OUTER JOIN or FULL JOIN
The FULL JOIN is used when we want ALL data from the left table OR the right table. It's the opposite of INNER JOIN. The highlighted green represents the full outer join or full join between two tables.
In this example, where we have two tables T1 (wild_animals1) and T2 (wild_animals2), we want to get wild animals that are present in both tables. If there is no matching data in either table, it simply returns NULL.
Let's see how to do this in SQL:
SELECT * FROM
wild_animals1
FULL JOIN wild_animals2
ON wild_animals1.Name = wild_animals2.Name
The resulting join table looks like this:
A word of advice: Developing SQL skills is no different from how one would develop any skill that requires practice, especially technical skills. The challenge with technical skills is that to keep them one must use them regularly. Simply put, use it or lose it. There is a ton of resources online where one could keep practicing during any down time. One of the most useful resources to practice SQL is here on www.w3schools.com
Drop me a line at [email protected] if you would like to have a quick discussion or need any help getting started with SQL.
If you are looking for a way to get started in the field of Analytics, Business Intelligence or RPA, feel free to drop us a line at [email protected]. We will be able to offer some advice or point to resources that can.