So far we have largely looked at selecting data from a single table. In this article, we will look at how data can be combined from multiple tables with the use of SQL joins. There are different ways of joining data together including the INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN and CROSS JOIN. We will look at how each of these joins works and then go through some practical examples to understand how to practically work with SQL joins.
Keywords: SQL JOINS – INNER, OUTER, LEFT, RIGHT, FULL and CROSS
SQL JOINS - INNER, OUTER, LEFT, RIGHT, FULL and CROSS table of contents
Learn SQL online
Inner Join
Let’s now look at the syntax. Below you can see that we place the keywords INNER JOIN after the first table that we want to join. Following this we specify the table that we want to join. Then we specify how to join the two tables together. In this case, we want to return records from table 1 and table 2 where they both have the same matching ID. This would commonly be done between the primary key in the first table and the foreign key in the second table. You can have more than one condition. We can add conditions that might be required to get an accurate match or for the purposes of filtering data in the second table in the same way you would add filter conditions in the where clause. You can use the keywords AND, NOT, and OR to add additional conditions.
SELECT Column1, Column2
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.ID = t2.ID AND
t1.Amount > 100;
Left and Right Outer join
An outer join works slightly differently from an INNER join. An inner join requires that rows from both tables match each other. An outer join will instead return all records from either the left-hand table or the right-hand table regardless if there is a match in the other table. If the two tables do match then the columns from both tables will be returned. If the two tables don’t match then data will be returned from either the left or right-hand table depending on which one has been specified. This can be seen in the ve-diagram below. we can see that in the case of the RIGHT OUTER JOIN all the data from the right table is returned along with any corresponding data from the other table if it matches. In the case of the LEFT OUTER JOIN situation is reversed and all the data from the left-hand side is returned pus any data that may match from the other table.
Left outer join
Right outer join
Let’s now look at the syntax of the left and right outer join. This time I have changed the layout to indicate which table is on the right-hand side and which is on the left. You can however choose any layout that you like and think makes the code more readable. The SQL has two tables LeftTable1 and RightTable2. One is on the left-hand side of the OUTER JOIN and one is on the right-hand side. Where we have used the LEFT OUTER JOIN all the data from the LeftTable1 will be returned regardless if there is a match to the other table. Where we have used RIGHT OUTER JOIN all the data from the RightTable2 will be returned regardless if there is a match to the LeftTable1. As is with the INNER JOIN we need to specify how the tables are related using the keyword ON and then specify the JOIN conditions. Again you can also add on multiple conditions with the AND, NOT and OR operators.
SELECT Column1, Column2
FROM LeftTable1 t1 LEFT OUTER JOIN RightTable2 t2
ON t1.ID = t2.ID;
SELECT Column1, Column2
FROM LeftTable1 t1 RIGHT OUTER JOIN RightTable2 t2
ON t1.ID = t2.ID;
Full outer join
The full outer join acts by combining the effects of the LEFT and RIGHT outer join. It will return all the data from both the LEFT and RIGHT tables. If the two tables have data that correspond then the columns from the other table will be returned if there is no match then columns from the mismathcing table will be shown as null. The ven-diagram below shows how all the data is returned.
To make a FULL OUTER JOIN use the the FULL OUTER JOIN term to join the left and right-hand tables together. Then specify the Join conditions as above.
SELECT Column1, Column2
FROM LeftTable1 t1 FULL OUTER JOIN RightTable2 t2
ON t1.ID = t2.ID;
Cross join
CROSS JOINS are rarely used but can be useful in specific circumstances. A CROSS JOIN acts by multiplying one table with another. Imagine that you have two tables each with 1 million records. If you were to apply a CROSS JOIN between these two tables you would end up with 1 trillion records returned. It goes without saying that this would have performance issues to consider. You could use a cross join if you wanted to represent a times table. By applying a cross join to two tables with the numbers 1 to 10 in each row you could return the 10 times tables in the result.
Let’s look at the syntax of the CROSS JOIN. Write the select statement as normal but this time specify the join condition CROSS JOIN. You will notice that there are now join conditions. This is because every row in the left table will be joined to every row in the right table and therefor no join conditions are needed. It will effectively multiply the two tables together.
SELECT Column1, Column2
FROM LeftTable1 t1 CROSS JOIN RightTable2 t2
Examples
1. Inner Join
An inner join combines rows from both tables where there’s a match based on a specified condition. For example, to get a list of employees along with their departments:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
The result will include only the rows where the DepartmentID matches in both tables, excluding the unpaired rows.
2. Outer Join:
A left outer join returns all rows from the left table and the matching rows from the right table. If there’s no match in the right table, the result will have NULL values for the right table’s columns. To see all employees and their departments, including those without departments:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
This query will return all employees, and if there’s a matching department, it will be displayed. If there’s no match, the DepartmentName will be NULL. In this example you can see that we omitted the OUTER keyword that is a short hand for LEFT OUTER JOIN.
4. Cross Join
A cross join creates a Cartesian product, combining each row from the first table with every row from the second table. It’s often used when you need to generate all possible combinations. To list all employees paired with all departments:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments
This query will result in every possible combination of employees and departments, providing a comprehensive dataset.
Conculsion
The choice of which join to use depends on the specific data analysis or reporting task at hand. SQL joins offer a powerful and flexible way to connect data, providing the means to transform, and derive insights from your relational database. Mastering these join types equips you with the skills to craft efficient queries, ensuring you retrieve precisely the data you need to meet your objectives. Whether you’re managing customer relationships, tracking inventory, or analyzing sales data, SQL joins are an essential tool in your database toolkit.
Please see the other SQL learning articles below as part of this learning SQL online course.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
