SQL Standard

Merge Two Tables in SQL

In SQL, table merging refers to the process of combining the data from two separate tables in a given database into a single unit based on a common column or criteria. Yes, if it sounds like a table, that is exactly what it is.

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:

CREATE TABLE employees (


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:

INSERT INTO employees (first_name, last_name, department) VALUES

('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:

CREATE TABLE salaries (


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:

INSERT INTO salaries (employee_id, salary, start_date, end_date) VALUES
(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.


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:








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:


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.








employees e

LEFT JOIN salaries s


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.


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:

SELECT employee_id , first_name , last_name , department , NULL AS salary

FROM employees e


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.


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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list