SQL Joins: Understanding INNER, LEFT, RIGHT, and FULL Joins

In this tutorial, you’ll learn about the different types of SQL joins, including inner, left, right and full with SQL statements provided for each join type to demonstrate how to perform them.

What are SQL Joins? #

SQL joins allow multiple tables to be combined in a single query. This leverages the power of relational databases to efficiently retrieve results that merge data from multiple tables.

Types of Joins #

INNER JOIN #

Returns only the matching records from both tables.

In the SQL statement below, the INNER JOIN ensures that only rows with matching id values in both tableA and tableB are included in the result.If a row in tableA does not have a corresponding row in tableB, it will be excluded (and vice versa).

SELECT a.column1, b.column2
FROM tableA a
INNER JOIN tableB b ON a.id = b.id;

LEFT JOIN (LEFT OUTER JOIN) #

Returns all records from the left table and matching records from the right table. If no match is found, NULL values are returned.

The LEFT JOIN ensures that all rows from tableA (the left table) are included in the result, even if there is no matching row in tableB. If a match is found in tableB, the corresponding values are included; otherwise, NULL values are returned for columns from tableB.

SELECT a.column1, b.column2
FROM tableA a
LEFT JOIN tableB b ON a.id = b.id;

RIGHT JOIN (RIGHT OUTER JOIN) #

Returns all records from the right table and matching records from the left table. If no match is found, NULL values are returned.

The RIGHT JOIN ensures that all rows from tableB (the right table) are included in the result, even if there is no matching row in tableA. If a match is found in tableA, the corresponding values are included; otherwise, NULL values are returned for columns from tableA.

SELECT a.column1, b.column2
FROM tableA a
RIGHT JOIN tableB b ON a.id = b.id;

FULL JOIN (FULL OUTER JOIN) #

Returns all records when there is a match in either left or right table. If no match exists, NULL values are returned from the unmatched table.

The FULL JOIN (or FULL OUTER JOIN) returns all records from both tables.

• If there is a match between tableA and tableB on id, the corresponding values from both tables are included.
• If no match is found, NULL values are returned for the columns of the table that lacks a matching row.

SELECT a.column1, b.column2
FROM tableA a
FULL JOIN tableB b ON a.id = b.id;

When to Use Joins #

• Use INNER JOIN when only matching records are needed.
• Use LEFT JOIN when all records from the left table are required.
• Use RIGHT JOIN when all records from the right table are required.
• Use FULL JOIN to get all records from both tables.

Conclusion #

Joins are a fundamental part of SQL that allow for powerful and flexible data retrieval. Understanding when to use each type helps optimize query performance and achieve desired results.