SQL Standard

SQL Join on Multiple Columns

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)
);

Next, create another table containing employment information as shown in the query below:

CREATE TABLE employment(
    id serial,
    department VARCHAR(50),
    active bool
);

Finally, create a table containing salary information as shown below:

CREATE TABLE salary(
    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);

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

This should return:

Thanks for reading; see in the next one.

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