LEFT JOIN vs RIGHT JOIN decision in SQL

Introduction

We use LEFT JOIN or RIGHT JOIN to combine data from two tables, keeping all rows from one table and matching rows from the other.

When you want all records from the first table and matching records from the second.

When you want all records from the second table and matching records from the first.

When you want to find unmatched records in one table compared to another.

When you want to keep the order or perspective of one table's data in your results.

Syntax

SQL

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

LEFT JOIN keeps all rows from the first (left) table.

RIGHT JOIN keeps all rows from the second (right) table.

Examples

This shows all employees and their departments. If an employee has no department, they still appear with NULL for department.

SQL

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

This shows all departments and employees in them. Departments without employees still appear with NULL for employee name.

SQL

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id;

Sample Program

This query lists all employees with their departments. Employees without a department show NULL in the department column.

SQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT);
CREATE TABLE departments (id INT, name VARCHAR(20));

INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', NULL), (3, 'Charlie', 20);
INSERT INTO departments VALUES (10, 'HR'), (20, 'IT'), (30, 'Sales');

SELECT employees.name AS employee, departments.name AS department
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

Important Notes

You can rewrite a RIGHT JOIN as a LEFT JOIN by switching table order.

LEFT JOIN is more common and easier to read for beginners.

Use LEFT JOIN or RIGHT JOIN depending on which table's data you want to keep fully.

Summary

LEFT JOIN keeps all rows from the first table, RIGHT JOIN keeps all from the second.

Choose LEFT or RIGHT JOIN based on which table's data you want to keep completely.

You can switch table order to use only LEFT JOIN if preferred.