SQL join multiple tables is one of the most popular types of statements executed while handling relational databases. As known, there are five types of join operations: Inner, Left, Right, Full and Cross joins.
In this article, we will explain the meaning of Joins in SQL, we will describe each one of the Join operation types and we will show the main use cases where it is used by providing examples.
Why using Joins?
Joins are used to combine the rows from multiple tables using mutual columns. As an example, assume that you have two tables within a database; the first table stores the employee’s information while the second stores the department’s information, and you need to list the employees with the information of the department where they are working. In that case, you must find a way to SQL Join multiple tables to generate one result set that contains information from these tables. Noting that joins can be applied over more than two tables.
To apply join between two tables, one table must contain a column that is a reference for the other table. In the example above, the Employees table must have a column that contain a reference key for the department (ex: Department id).
As mentioned above, there are multiple approaches to SQL join multiple tables. We will describe each approach briefly in the next sections.
Creating tables used in examples
To illustrate different types of joins, we will create Employees and Departments tables as following:
- Employees table:
- Employee_id (int) / identifier
- Employee_name (varchar)
- Employee_DOB (date)
- Department_Id (reference to departments table)
- Departments table:
- Department_id (int) / identifier
- Department_Name (varchar)
There are four departments defined within the Departments table:
- Human resources
- Development
- Sales
- Technical Support
And there are five employees defined within the Employees table:
- Alan Smith (Department: Human Resources)
- Sultan Nader (Department: Human Resources)
- Mohd Rasheed (Department: Development)
- Brian Wallace (Department: Sales)
- Peter Hilton (Not assigned to a department until now)
We have used the following commands to create this example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Create employees table CREATE TABLE #Employees(Employee_id int, Employee_name varchar(250), Employee_DOB date, Department_ID int) -- Create departments table CREATE TABLE #Departments(Department_id int, Department_Name varchar(250)) -- Insert values into departments table INSERT INTO #Departments(Department_id,Department_Name) VALUES(1,'Human Resources'), (2,'Development'), (3,'Sales'), (4, 'Technical Support') -- Insert values into employees table INSERT INTO #Employees(Employee_id,Employee_name, Employee_DOB,Department_ID) VALUES (1,'Alan Smith','19890101',1), (2,'Sultan Nader','19920101',1), (3,'Mohd Rasheed','19990101',2), (4,'Brian Wallace','19790101',3), (5,'Peter Hilton','19860101',NULL) |
INNER JOIN
Inner join is the most popular join type, it selects the rows where values are the mutual columns values are matched. If we apply an inner join to the Employees-Departments example, it will only return the employees working within departments that have a row within the Departments table:
Back to the Employees and Departments tables, to select the employees that are working within departments we can use the following query:
|
1 2 3 |
SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Departments INNER JOIN #Employees ON #Departments.Department_id = #Employees.Department_ID |
As shown in the query above, first we need to specify the columns we want to retrieve within the SELECT clause, then we need to specify the tables we need to read from and to specify the join type within the FROM clause, also we need to specify the columns used to perform the join operation after the ON keyword. The result of the query mentioned is as shown in the following screenshot:
From the screenshot above, we can see that the fifth employee is not shown in the result since it is not assigned to any department. Also, we can note that the Department_Name is retrieved instead of the Department id.
LEFT JOIN
Another SQL join multiple tables approach, is LEFT JOIN which is used to retrieve all rows from the first table mentioned in the FROM clause in addition to the matched rows from the second table:
Back to the Employees and Departments tables, if we need to select all employees listed within the Employees table and to mention the department name if exists, we can use the following query:
|
1 2 3 |
SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Employees LEFT JOIN #Departments ON #Departments.Department_id = #Employees.Department_ID |
The query result is as shown in the following screenshot:
As we can see, the query result returned all five employees listed within the table with a NULL value in the Department_Name column in the fifth row since Peter Hilton is not assigned to any department yet.
RIGHT JOIN
The next SQL join multiple tables approach is RIGHT JOIN, which is very similar to LEFT JOIN since it returns all rows from the table listed at the right of the JOIN operator with the matched values from the table listed at the left:
Back to the Employees and Departments tables, if we need to select the employees that are working within departments, in addition to departments that does not have any employees, we can use the following query:
|
1 2 3 |
SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Employees RIGHT JOIN #Departments ON #Departments.Department_id = #Employees.Department_ID |
As shown in the screenshot below, the query returned the same rows of the INNER JOIN query in addition to the Technical support department that doesn’t have any employee:
FULL OUTTER JOIN
FULL OUTTER JOIN is another approach used to SQL join multiple tables. It returns all matched rows between both tables specified in the JOIN operation in addition to all unmatched rows from the first and second tables:
Back to the Employees and Departments tables, the Full join query will return all employees working within departments plus all employees that are not assigned and all departments that doesn’t contain any employee:
|
1 2 3 |
SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Employees FULL JOIN #Departments #Departments.Department_id = #Employees.Department_ID |
From the screenshot below, we can see that Peter Hilton is with no value in the Department_Name field, and Technical support department is shown with no employee information:
CROSS JOIN
The last approach used to SQL Join multiple tables is CROSS join which is a bit different from the other Join operations. It is used to create a combination of two different sets without have mutual columns. As an example, if we need to create a combination of all departments with all employees.
Example:
|
1 2 3 |
SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Employees CROSS JOIN #Departments |
Result:
Conclusion
Joining table is one of the main uses of SQL language. In this article, we have explained why using Joins, and we illustrated five different approaches to SQL Join multiple tables by providing some examples. We noted that Inner, Left, Right, and Full joins require mutual columns between tables while Cross join is to multiply to rows of the first table with the ones stored in the second table.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023