In some cases, you may encounter a scenario where you need to perform a join on multiple columns.
This article will understand how to do this using a practical example.
Practical Example
The best way to understand how to join multiple columns is to use an example.
Start by creating a sample users table as shown below:
CREATE TABLE employees(
id serial,
first_name VARCHAR(255),
last_name VARCHAR(255)
);
id serial,
first_name VARCHAR(255),
last_name VARCHAR(255)
);
Next, create another table containing employment information as shown in the query below:
CREATE TABLE employment(
id serial,
department VARCHAR(50),
active bool
);
id serial,
department VARCHAR(50),
active bool
);
Finally, create a table containing salary information as shown below:
CREATE TABLE salary(
id serial,
salary INT
);
id serial,
salary INT
);
We can then add sample data as provided in the statements below:
INSERT INTO employees(first_name, last_name) VALUES
('Rosie', 'Nunez'),
('Cecil', 'Pearson'),
('Eugene', 'Butler');
INSERT INTO employment(department, active) VALUES
('Game development', TRUE),
('Database Developement', TRUE),
('Back-End Development', TRUE);
INSERT INTO salary(salary) VALUES
(100000),
(200000),
(108000);
('Rosie', 'Nunez'),
('Cecil', 'Pearson'),
('Eugene', 'Butler');
INSERT INTO employment(department, active) VALUES
('Game development', TRUE),
('Database Developement', TRUE),
('Back-End Development', TRUE);
INSERT INTO salary(salary) VALUES
(100000),
(200000),
(108000);
Once we have all the tables set up, we can perform a join on multiple columns as shown in the query below:
SELECT
e.first_name, e.last_name,
em.department, em.active
FROM employment em
JOIN employees e
ON e.id=em.id
JOIN salary s
ON s.id=em.id
e.first_name, e.last_name,
em.department, em.active
FROM employment em
JOIN employees e
ON e.id=em.id
JOIN salary s
ON s.id=em.id
This should return:
Thanks for reading; see in the next one.
