DSPython Logo DSPython

SQL Joins

Learn to combine data from multiple tables using INNER, LEFT, RIGHT, and FULL Joins.

SQL Intermediate 60 min

🤝 Introduction: Combining the Data

In any real-world scenario, data is stored across multiple tables. For example, one table stores Customer Details, and another stores Order History. We use SQL JOINs to combine these tables based on a common field (like CustomerID).

A **JOIN** operation combines columns from two or more tables into a single result set based on a related column between them.

📝 JOIN Syntax:

SELECT Columns FROM TableA JOIN TableB ON TableA.Key = TableB.Key;

🎯 Topic 1: INNER JOIN (The Intersection)

The **INNER JOIN** is the most common type. It returns only the rows that have matching values in *both* tables. It ignores any records that do not have a match in the other table.

✅ Purpose:

To see the **overlap**—for example, which customers *have* placed an order. Customers without orders are ignored.

💻 Example: Finding Matching Records

SELECT T1.Name, T2.OrderDate
FROM Customers T1
INNER JOIN Orders T2
ON T1.CustomerID = T2.CustomerID;

⬅️ Topic 2: LEFT JOIN (Preserving the Left Table)

A **LEFT JOIN** returns all records from the left table (`Customers`), and the matching records from the right table (`Orders`). If there is no match in the right table, the columns from the right table will contain **NULL**.

This is used when you want to preserve all your primary data—for example, getting a list of *all customers*, regardless of whether they have placed an order yet.

💻 Example: All Customers and Their Orders

SELECT T1.Name, T2.OrderID
FROM Customers T1
LEFT JOIN Orders T2
ON T1.CustomerID = T2.CustomerID;

➡️ Topic 3: RIGHT and FULL Joins

These are similar to the LEFT JOIN, but their focus shifts:

1. RIGHT JOIN (Preserving the Right Table):

Returns all records from the right table, filling in NULL where there's no match on the left. (e.g., Finding all Orders, even if the customer ID is missing/invalid).

2. FULL OUTER JOIN (The Union):

Returns all records when there is a match in *either* the left or the right table. It's the union of all data. NULL is returned for cells without a match.

💻 Example: FULL OUTER JOIN

-- List all Customer IDs and all Order IDs, showing NULLs where no match exists
SELECT T1.CustomerID, T2.OrderID
FROM Customers T1
FULL OUTER JOIN Orders T2
ON T1.CustomerID = T2.CustomerID;

⚖️ Topic 4: JOIN vs UNION

The **UNION** operator is often confused with **JOIN**. They perform entirely different tasks:

1. JOIN:

Combines data **horizontally** (adds columns) based on a key. Requires a related column.

2. UNION:

Combines data **vertically** (adds rows) from two or more tables. Requires tables to have the same number of columns and compatible data types.

💻 Example: UNION (Combining Rows)

-- Combine employee names from two different tables, removing duplicates
SELECT Name, City FROM Employees_NY
UNION
SELECT Name, City FROM Employees_CA;

-- Use UNION ALL to keep duplicate names

📚 Module Summary

  • INNER JOIN: Matching records in both tables (Intersection).
  • LEFT JOIN: All records from the left table + matches from the right.
  • FULL JOIN: All records from both tables (Union).
  • JOIN vs UNION: JOIN adds columns (Horizontal); UNION adds rows (Vertical).

🤔 Interview Q&A

Tap on the questions below to reveal the answers.

The key purpose of a LEFT JOIN is to preserve all records from your primary (left) table, and only add the matching information from the right table. This is essential for reporting on all customers, regardless of whether they have matching sales data.

A JOIN combines data horizontally (adding columns) based on a key, and typically results in a wider table. A UNION combines data vertically (adding rows) and requires the tables to have the same column structure.

NULL values will never appear in the columns being used for the join key in an INNER JOIN, because the join only returns rows where a match exists in both tables. However, other non-key columns can still contain NULL values.

Yes. The standard UNION operator automatically removes duplicate rows from the final result set. If you wish to retain all duplicate rows, you must use the UNION ALL operator instead.