A table join or a table merge is a renowned feature of relational databases, and it is incredibly powerful. It allows us to consolidate the information from multiple sources to create more coherent and meaningful data insights. It also allows the relational databases to be highly scalable (not flexible) as we can break down the data into smaller, manageable chunks which we can reference later.
In this tutorial, we will cover the fundamentals of table joins or table merge. Let’s look at the real-world table samples to solidify our knowledge.
Sample Table
Before we go into the world of table joins, let us setup the basic tables which we will use for demonstration purposes.
Consider two tables that contains the employees and salary information as shown in the following example queries:
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);
We can then insert the sample data into the employee’s table as shown in the following queries:
('Alice', 'Smith', 'Human Resources'),
('Bob', 'Johnson', 'Marketing'),
('Charlie', 'Wilson', 'Finance'),
('David', 'Brown', 'Sales'),
('Eva', 'Davis', 'Engineering');
Let us proceed and create a new table to store the salary information as follows:
salary_id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
salary DECIMAL(10, 2),
start_date DATE,
end_date DATE,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
Add the insert sample data into the table as follows:
(1, 60000.00, '2023-01-01', '2023-12-31'),
(2, 55000.00, '2023-01-01', '2023-12-31'),
(3, 65000.00, '2023-01-01', '2023-12-31'),
(4, 58000.00, '2023-01-01', '2023-12-31'),
(5, 70000.00, '2023-01-01', '2023-12-31');
This should provide us with two tables that can help us to demonstrate the concept of table joins/merge in SQL.
SQL Table Merge/Table Joins
Let us explore the various types of table merges that we can do. We will cover the fundamental ones as we progress to more advanced ones.
INNER JOIN
The first and most common type of table join in SQL is an INNER JOIN. An INNER JOIN allows us to combine the rows from two tables based on a specific condition. This type then returns only the rows where there is a match between the tables.
Let us take the “employees” and the “salaries” tables that we created earlier as examples. To perform an INNER JOIN in SQL, we use the INNER JOIN clause as follows:
e.employee_id,
e.first_name,
e.last_name,
e.department,
s.salary
FROM
employees e
INNER JOIN salaries s ON
e.employee_id = s.employee_id;
In the given example query, we use an INNER JOIN to merge the “employees” and the “salaries” tables on the “employee_id” column which exists in both tables. The resulting set contains only the matching rows from both tables.
An example output is as follows:
LEFT OUTER JOIN
We also have a LEFT OUTER JOIN which combines all rows from the left table and the matching rows from the right table. If there is no match in the right table, the join uses the NULL value.
e.employee_id,
e.first_name,
e.last_name,
e.department,
s.salary
FROM
employees e
LEFT JOIN salaries s
ON
e.employee_id = s.employee_id;
In this example, we perform a LEFT OUTER JOIN to merge the “employees” and “salaries” tables. All the rows from the “employees” table are included and the matching rows from the “salaries” table are added. However, NULL values are included in the “salary” column for non-matching rows.
SQL UNION
Another method of joining the tables in SQL is using the UNION operator. This operator allows us to combine the results of two or more select statements into a single result set.
The columns in each SELECT statement must have the same data type for the union to be applicable.
An example is as follows:
FROM employees e
UNION
SELECT employee_id , NULL AS first_name , NULL AS last_name , NULL AS department , salary
FROM salaries s ;
In this case, a UNION merges the “employees” and “salaries” tables. We then create the NULL columns in each SELECT statement to ensure that both tables have a similar number of columns.
UNIONS are technically common but they can be useful especially when you need to merge the tables with different structures.
Conclusion
In this tutorial, we explored the fundamentals of joining/merging two tables into a single result set. It is good to keep in mind that there a lot more advanced joins that are discussed in this post.
