Types of JOINs in SQL

When working with relational databases, using JOINs is essential for combining data from multiple tables. In this blog post, we will explore the various types of JOINs in SQL, how they work, and when to use them to handle discrepancies in your database.

Why Use JOINs?

JOINs allow us to access fields from multiple tables, ensuring that we can retrieve comprehensive data for our queries. Understanding the different types of JOINs helps us decide whether to include or exclude unmatched data in our results.

Types of JOINs in SQL

1. INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. It excludes any rows where there is no match.

Example:

Imagine we have two tables, customers and invoices. The customers table has fields like customer_id, first_name, and last_name. The invoices table has fields like invoice_id, customer_id, and total.

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
INNER JOIN invoices AS i
ON c.customer_id = i.customer_id;

Explanation:

• INNER JOIN: Combines rows from both tables where the customer_id matches.
• c.customer_id = i.customer_id: Specifies the condition for the join.

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side.

Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
LEFT JOIN invoices AS i
ON c.customer_id = i.customer_id;

Explanation:

• LEFT JOIN: Includes all rows from the customers table, even if there is no match in the invoices table.
• NULL results: Rows from customers with no corresponding invoices will have NULL values for the invoice fields.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the left side.

Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
RIGHT JOIN invoices AS i
ON c.customer_id = i.customer_id;

Explanation:

• RIGHT JOIN: Includes all rows from the invoices table, even if there is no match in the customers table.
• NULL results: Rows from invoices with no corresponding customers will have NULL values for the customer fields.

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all rows when there is a match in either left or right table. If there is no match, the result is NULL for the unmatched rows.

Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
FULL JOIN invoices AS i
ON c.customer_id = i.customer_id;

Explanation:

• FULL JOIN: Includes all rows from both customers and invoices tables, with NULL values for unmatched rows.

Handling Discrepancies

Real-World Scenario

Consider this scenario: a customer, customer 6, deletes their WSDA music account and is removed from the customers table. However, the invoices table still shows that customer 6 made a purchase. This is a common discrepancy in databases.

Using Different JOINs

By using different JOINs, we can decide how to handle such discrepancies:

• INNER JOIN: Excludes unmatched records.
• LEFT JOIN: Includes all records from the left table, even if there’s no match.
• RIGHT JOIN: Includes all records from the right table, even if there’s no match.
• FULL JOIN: Includes all records from both tables, with NULLs for unmatched records.

Practical Application

INNER JOIN Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
INNER JOIN invoices AS i
ON c.customer_id = i.customer_id;

LEFT JOIN Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
LEFT JOIN invoices AS i
ON c.customer_id = i.customer_id;

RIGHT JOIN Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
RIGHT JOIN invoices AS i
ON c.customer_id = i.customer_id;

FULL JOIN Example:

SELECT c.first_name, c.last_name, i.invoice_id, i.total
FROM customers AS c
FULL JOIN invoices AS i
ON c.customer_id = i.customer_id;

FAQs