Understanding SQL Joins: INNER, LEFT, RIGHT & FULL Explained

The Joins in SQL are among the most powerful tools that allow us to combine data from multiple related tables. Using Joins, we can extract more complete and accurate information without duplicating data within a single table.

Why Do We Use Joins? And What Are the Types of SQL Joins?

Data in databases is often distributed across several related tables. Joins help us access these related records through a single query that connects shared values and displays organized results.

INNER JOIN

Returns only the rows that meet the join condition between both tables — i.e., it shows only matching values.

SELECT customers.name, orders.order_date 
FROM customers
INNER JOIN orders 
ON customers.id = orders.customer_id;

LEFT JOIN (or LEFT OUTER JOIN)

Shows all rows from the left table, even if there are no matches in the right table. If no match exists, the right table’s columns appear as NULL.

SELECT customers.name, orders.order_date 
FROM customers
LEFT JOIN orders 
ON customers.id = orders.customer_id;

RIGHT JOIN (or RIGHT OUTER JOIN)

Displays all rows from the right table, even if no matching record exists in the left table. Unmatched rows will contain NULL values in the left table’s columns.

SELECT customers.name, orders.order_date 
FROM customers
RIGHT JOIN orders 
ON customers.id = orders.customer_id;

FULL JOIN (or FULL OUTER JOIN)

Combines the results of both LEFT JOIN and RIGHT JOIN, returning all rows whether matches exist or not.

SELECT customers.name, orders.order_date 
FROM customers
FULL OUTER JOIN orders 
ON customers.id = orders.customer_id;

Comparison Between SQL Join Types

Conclusion

Understanding SQL Join types helps you write efficient queries and extract accurate data. Choosing the right join depends on the information you want: shared only (INNER JOIN), all from one side (LEFT/RIGHT JOIN), or complete results from both (FULL JOIN).