Mastering Commonly Used SQL Joins: With Relatable Examples

Introduction

SQL is a defacto language when it comes to interacting with data in Data Storage containers such as relational databases, data warehouses, data lakes etc.

Whether you are professional such as a data engineer, analyst, scientist, analytics engineer or a motivated pal interested in tinkering with data, one needs to master SQL Joins.

For SQL joins to work, a relationship needs to be identified in order to join data from different tables. This is achieved using foreign and primary keys.

In this guide we'll look at how joins work using PostgreSQL as our database management system and DBeaver as our SQL Editor.

Primary vs Foreign Keys

A primary key is a specific column (or a combination of columns) that uniquely identifies every record in a database table.
A Foreign Key is a column in one table that points to the primary key of another table, creating a "link" between them.

The Example below cleary shows the primary and foreign keys in related tables.

Database name: Company

Tables:

  - Employees
  - Departments
  - Projects

Fig 1. Employees Table Columns

Columns Preview:

employee_id <-- Primary Key
name
department_id <-- Foreign Key
manager_id    <-- Foreign Key
salary

Fig 2. Departments Table Columns

Columns Preview:

department_id <-- Primary Key
department_name

Fig 3. Projects Table Columns

Columns Preview:

project_id <-- Primary Key
project_name
employee_id  <-- Foreign Key

SQL Joins Deep Dive

We have a variety of ways to compile data from table(s) using different types of joins. Examples Include:

1. INNER JOIN

It returns rows only when there is a match in both tables. If an employee isn't assigned to a project, they won't show up here.

Example:

select e.name,  p.project_name
from employees as e
inner join projects as p
on e.employee_id = p.employee_id;

Output:

NOTE: Use only when you want records that are complete ("Find all employees and their assigned projects").

2. LEFT JOIN

It returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.

Example:

select e.name,  p.project_name
from employees as e
left join projects as p
on e.employee_id = p.employee_id;

Output:

3. RIGHT JOIN

It returns all records from the right-hand table and the matched records from the left-hand table. If there is no match for a record in the right table, the result will contain NULL values for the columns originating from the left table.

Example:

select  d.department_name, e.name
from departments as d
right join employees as e
on d.department_id = e.department_id;

Output:

4. FULL OUTER JOIN

It returns all records when there is a match in either left or right table records. It’s like a combination of Left and Right joins.

Example:

select e.name, d.department_name
from employees as e
full join departments as d
on e.department_id = d.department_id;

Output:

The above practical joins are the most commonly used when querying data, other joins include:

• Cross Joins
• Self Joins

Conclusion

Understanding Joins is more than just connecting tables, it’s about writing efficient, scalable queries. Whether you’re mapping organizational data, the goal is to have clean data retrieval with minimal overhead. Happy querying!