intro
For this guide, we will discuss the different types of SQL Joins there are, cover their syntax and learn how to implement them at the right time using a tool like DbVisualizer - have a read!
A SQL Join is a statement that is used to join data from two or more tables in a database based on a mutual factor or element between them. This mutual element may be a column. There are various types of SQL Joins. The various types are the (Inner) Join, Left (Outer) Join, Right (Outer) Join, Full Join, Self Join and Cross Join. Although Inner Join, Left Join, Right Join, and Full Join are the four main types, there are also Self joins and Cross joins which are also important joins to know, and are applicable in certain scenarios.
In this cheat sheet, you'll come across a handy list of functions covering SQL Joins. Have the cheat sheet below at your fingertips.
Introduction to SQL Join Cheat Sheet
SQL joins are very important components of databases and their importance today cannot be overstated. In modern businesses that rely on data-informed decision-making, SQL joins do play a crucial role in facilitating complex data analysis. This is clearly capitalized when joins play useful roles such as avoiding the occurrence of data duplication, enhancing data retrieval processes, as well as supporting data integration, where data from different sources are combined together into a single, unified view. As such, database developers need to understand the rightful implementation of SQL joins as they form integral parts of working with relational databases.
Now that we have established the importance of SQL joins, let us try to understand this SQL cheat sheet by looking at the various types of SQL joins, identifying the syntax and the logic behind each, and how they can be employed to save the day.
Types of Joins Explained
As discussed in an earlier chapter of this blog, there are different types of SQL joins. This section explains their syntax and the logic behind their implementations. For illustrative purposes explaining the types of SQL joins, let us assume we have a database called Employees. The tables below are selections from Table 1 and Table 2 of the database.
Note: These tables are solely for illustrative purposes and users should feel free to use their own datasets or values.
Table 1
| EmployeeID | FirstName | LastName | DepartmentID |
|---|---|---|---|
| 1004 | Mobie | Dick | 1 |
| 1005 | Susan | Halts | 2 |
| 1006 | Sally | Jane | 3 |
| 1007 | Miles | Drinkwater | 2 |
| 1008 | Joy | Gales | 1 |
Table 1
| DepartmentID | Department |
|---|---|
| 1 | Human Resource |
| 2 | Finance |
| 3 | Engineering |
| 4 | IT |
Inner Join
An INNER JOIN is one of the table joins in SQL and the simplest JOIN amongst them all. The INNER JOIN keyword selects records that have a common matching value between them. It selects all rows from both tables as long as the condition is met - that there’s a common matching value between them. If there are records in one table that do not have matches in the other table, there would be no result set. The syntax for an INNER JOIN is as follows:
1
SELECT column_name(s)
2
FROM table1
3
INNER JOIN table2
4
ON table1.column = table2.column;
The query above performs an inner join between table1 and table2 on a matching column and selects the specified column(s) from the resulting merged table. Users should note that they are to use their own datasets or values by replacing the table and column names with their own table and column names.
For example, let’s build a query that will return a table that includes the first name and last name of each employee in the ‘Table 1’ table along with the corresponding department from the ‘Table 2’ where the employee works.
1
SELECT Table1.FirstName, Table1.LastName, Table2.Department
2
FROM Employees Table1
3
INNER JOIN Departments Table2 ON Table1.DepartmentID = Table2.DepartmentID
Result:
If you perform the query using DbVisualizer this will be the format of the output.
DbVisualizer can also tell you the time the queries take to execute and the status of whether or not there is an error:
The INNER JOIN is a type of SQL join that allows you to combine data from two tables based on matching values in a specified column. Let us take a look at another type of join called the FULL JOIN which combines the behaviour of all the joins.
Full Join
A FULL JOIN returns all records when there is a match in the left (table1) or right (table2) table records. This simply means it creates the result set by combining the results of both LEFT JOIN and RIGHT JOIN. However, the result will be NULL for the rows for which there is no match. Below is the syntax for a FULL JOIN:
1
SELECT column_name(s)
2
FROM table1
3
FULL OUTER JOIN table2
4
ON table1.column_name = table2.column_name
5
WHERE condition;
For an example, let us build a query that will return a result set that includes all records from both Table 1 and Table 2, including the records where there is no match in the other table. If an employee is not associated with any department or if a department has no employees, then the corresponding values for that table will be NULL in the result set.
1
SELECT Table1.FirstName, Table1.LastName, Table2.Department
2
FROM Employees Table1
3
FULL OUTER JOIN Departments Table2 ON Table1.DepartmentID = Table2.DepartmentID
Result:
Full Join with Filter Clause
1
SELECT table1.column1, table1.column2
2
FROM table1
3
FULL JOIN table2 ON table1.key = table2.key
4
WHERE table1.key IS NULL OR
5
table2.key IS NULL;
You could also use this FULL JOIN with an additional filter clause WHERE table1.key IS NULL OR table2.key IS NULL to return all columns from table 1 where there is a match in table 2 based on the key column. Here, the WHERE clause will filter the results to include rows where there’s no match on either side of the join. Now that we have covered the FULL JOIN, let us move on to the LEFT JOIN.
Left Join
A LEFT JOIN, sometimes referred to as a LEFT OUTER JOIN, returns all records from the left side of the join (table1), and the matching records from the right side of the join (table2). The result is NULL from the right side if there is no match. The term "outer" is simply used to differentiate this type of join from an INNER JOIN, which only returns rows that have matching values in both tables being joined. The syntax for a LEFT JOIN is as follows:
1
SELECT column_name(s)
2
FROM table1
3
LEFT JOIN table2
4
ON table1.column_name = table2.column_name;
For an example, let's build a query that will combine data from two tables, Employees and Department, based on the DepartmentID column. For users to use this query for their own tables, users would need to replace table1, table2, column1, column2, column3, columnX, and columnY with their own table and column names.
1
SELECT Table1.FirstName, Table1.LastName, Table2.Department
2
FROM Employees Table1
3
LEFT JOIN Departments Table2 ON Table1.DepartmentID = Table2.DepartmentID
The resulting table will contain all the rows from Table 1 and any matching rows from Table 2. If there is no match for an employee in Table 2 (if an employee does not belong to any department), the corresponding row in the result set will contain null values for the Department column.
Result:
Left Join with Filter Clause
1
SELECT table1.column1, table1.column2
2
FROM table1
3
LEFT JOIN table2 ON table1.key = table2.key
4
WHERE table2.key IS NULL;
You could also use this LEFT JOIN code with an additional filter clause added to select only the records where there is no matching record in Table 2. This query would return all rows from table1 and any matching rows from table2 based on the join condition, where the key value in table2 is null. If there are no matching rows in Table 2, the result will still include the rows from Table 1, but the columns from Table 2 will be NULL.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
The LEFT JOIN is a powerful keyword for retrieving data from two or more tables based on a common column. It returns all the records from the left table and matching records from the right table. If there are no matching records in the right table, NULL values are returned. Now, let's move on to the next type of join: the RIGHT JOIN.
Right Join
Similar to LEFT JOIN, the RIGHT JOIN also known as RIGHT OUTER JOIN, returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. The result is NULL from the left side if there is no match. Below is the syntax for a RIGHT JOIN:
1
SELECT column_name(s)
2
FROM table1
3
RIGHT JOIN table2
4
ON table1.column_name = table2.column_name;
Let us build a query that will retrieve the first name and last name of all employees, along with their department name, by performing a RIGHT JOIN on the employees and department tables. For users to use this query for their own tables, users would need to replace table1, table2, column1, column2, column3, columnX, and columnY with their own table and column names.
1
SELECT Table1.FirstName, Table1.LastName, Table2.Department
2
FROM Employees Table1
3
RIGHT JOIN Departments Table2 ON Table1.DepartmentID = Table2.DepartmentID
This query will select all records from Table 2 and only the matching records from Table 1. It returns all the records from the right table (Table 2), and only the records from the left table (Table 1) that have a matching value in the DepartmentID column. If there are no matching records in the left table, NULL values are returned for the corresponding columns. The query basically says "Show me all the departments, and if there are any employees in that department, show me their first and last names too".
Result:
Right Join with Filter Clause
1
SELECT table1.column1, table1.column2
2
FROM table1
3
RIGHT JOIN table2 ON table1.key = table2.key
4
WHERE table1.key IS NULL;
You could also use this RIGHT JOIN with an additional filter clause WHERE table1.key IS NULL to only return the rows from table2 that have no matching key in table1.
The RIGHT JOIN returns all the records from the right table and matching records from the left table, with NULLs in the columns where there is no match. It is useful when you want to see all the records from the right table, regardless of whether there are corresponding records in the left table. Now that we have covered the four major types of joins, we will move on to the cross join, which is a bit different from what we have seen so far.
Cross Join
A CROSS JOIN returns the combination of each row from one table with each row from another table. For example, considering our database, the CROSS JOIN will produce a result set that combines every employee with every department. Our query will look like so:
1
SELECT Table1.FirstName, Table1.LastName, Table2.Department
2
FROM Employees Table1
3
CROSS JOIN Departments Table2;
Result:
It is important to note that cross joins can quickly become computationally expensive and should be used with caution. This is because a cross join generates a result set that is a combination of every row from one table with every row from another table, resulting in a very large output. As a result, performing a cross join on tables with a large number of rows can be resource-intensive (memory and processing power) and take a long time to complete.
Self Join
A self join is a regular join but is used when a table has a foreign key relationship with itself so that the table is joined with itself instead of another table. It is typically useful when you need to compare rows within the same table. Below is the syntax for a self join:
1
SELECT column_name(s)
2
FROM table1 T1, table1 T2
3
WHERE condition;
Where T1 and T2 are different aliases for the same table. To look at an example of a self join, we will take another version of the Employee table below:
| EmployeeID | FirstName | LastName | SupervisorID |
|---|---|---|---|
| 1 | Mobie | Dick | 2 |
| 2 | Susan | Halts | 3 |
| 3 | Sally | Jane | 4 |
| 4 | Miles | Drinkwater | 2 |
| 5 | Joy | Gales | 1 |
Here, we’re using the Employee table twice. The first and last names of the employee are selected from the first instance of the table ("e1"), and the first and last names of the employee's supervisor are selected from the second instance of the table ("e2"). The WHERE clause specifies the condition for the join, which is that the Supervisor ID in the first instance of the table (e1.supervisor_id) must match the Employee ID in the second instance of the table (e2.employee_id). This is what the results of our query will look like:
From this result set, we can see that:
By using a self join, we can have a table (result set) that shows all the names of employees and their supervisors even though the information is stored in the same table.
Conclusion
SQL joins are a powerful tool for working with relational databases, allowing you to combine data from multiple tables using a single query. Whether you're working with simple queries or complex, multi-table joins, having a solid understanding of the different types of SQL joins is essential for any data analyst or database developer.
By following this SQL cheat sheet, you can quickly reference the syntax and usage of each type of join, from the basic INNER JOIN to more advanced types like LEFT (OUTER) JOIN and SELF JOIN. With this knowledge, you'll be equipped to tackle even the most challenging SQL join scenarios in the database space.
Although SQL joins are an important aspect of database development, they're just one piece of the puzzle. To truly optimize your database performance, availability, capacity, and security, it's important to employ a range of tools and techniques. Solutions like DbVisualizer offer a powerful SQL client with advanced features and support for a wide range of database management systems. With the right tools and expertise, you can take your database development to the next level. Grab the free evaluation trial and get hands-on experience with the features!
What are the different types of SQL joins?
There are several types of SQL joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN. Each type of join serves a different purpose.
Can I join more than two tables in SQL?
Yes, you can join more than two tables in SQL. To join multiple tables, you can simply include additional JOIN clauses in your SQL statement, with each JOIN clause joining one table to the result of the previous join. For instance:
1
SELECT *
2
FROM orders
3
JOIN customers
4
ON orders.customer_id = customers.customer_id
5
JOIN products
6
ON orders.product_id = products.product_id;
This query will join the orders table with the customers table on the customer_id field, and then join the result set with the products table on the product_id field.
How do I choose the right type of join for my query?
The right type of join for your query depends on the relationship between the tables you are joining and the type of data you want to return. Knowing the result set of each of the JOIN types is essential to choosing the right type of join for your query.
How do I join multiple tables in SQL?
To join multiple tables in SQL, you can use the JOIN keyword multiple times in your query, joining each subsequent table to the previous result set. For instance:
1
SELECT *
2
FROM orders
3
JOIN customers ON orders.customer_id = customers.customer_id
4
JOIN products ON orders.product_id = products.product_id;
Here, three tables - orders, customers, and products are being joined together based on their individual foreign keys. The result will be a single table that contains columns from all three tables.
