Lesson 5: Subqueries vs Joins (When Each Is Appropriate)

Section 3 — Lesson 5: Subqueries vs Joins (When Each Is Appropriate)

Welcome back. You now understand how joins connect tables, the join types, and the join patterns DBAs use.
The next skill is knowing when to use a join and when to use a subquery.

In real DBA work, both approaches are common. The goal is not “always joins” or “always subqueries.”
The goal is: correct results, clear intent, and safe performance.

Lesson description: You will learn what a subquery is, the main subquery types (scalar, multi-row, correlated),
how joins and subqueries differ in how they express intent, when each is the best tool, and which patterns DBAs prefer
for safety (NOT EXISTS instead of NOT IN, APPLY for advanced cases, avoiding subqueries that hide row multiplication).

1) What Is a Subquery?

A subquery is a query inside another query. It can appear in places like:

• the SELECT list (return a single value per row)
• the WHERE clause (filter rows based on another query)
• the FROM clause (a derived table / inline view)
• WITH clause (CTEs — you’ll learn later, but conceptually similar to derived tables)

A join combines tables into a single rowset and then filters/aggregates.
A subquery often answers a “secondary question” used to filter or compute values.

DBA mindset: choose the form that expresses your intent clearly and avoids hidden duplication.

2) Subquery Types DBAs Must Recognize

2A) Scalar subquery (returns a single value)

A scalar subquery returns exactly one value. It is often used to compute a reference value.

-- Return each booking plus the overall average booking amount (same value on every row)
SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.TotalAmountUSD,
    (SELECT AVG(TotalAmountUSD) FROM dbo.Booking) AS OverallAvgBookingAmountUSD
FROM dbo.Booking AS b
ORDER BY b.BookingID;

DBA caution: scalar subqueries in SELECT can be expensive if they are correlated (next type).

2B) Multi-row subquery (returns many values)

A multi-row subquery is typically used with IN or EXISTS.

-- Find bookings whose CustomerID is in the list of customers with a known email domain
SELECT
    b.BookingID,
    b.CustomerID,
    b.TotalAmountUSD
FROM dbo.Booking AS b
WHERE b.CustomerID IN
(
    SELECT c.CustomerID
    FROM dbo.Customer AS c
    WHERE c.Email LIKE '%@example.com'
)
ORDER BY b.BookingID;

This is correct, but sometimes a join can be clearer (you’ll compare soon).

2C) Correlated subquery (references the outer query)

A correlated subquery runs “per row” conceptually because it references the outer query’s columns.
This pattern is common, but can be slow if used carelessly.

-- Return each customer and their booking count (correlated subquery)
SELECT
    c.CustomerID,
    c.FullName,
    (SELECT COUNT(*) FROM dbo.Booking AS b WHERE b.CustomerID = c.CustomerID) AS BookingCount
FROM dbo.Customer AS c
ORDER BY c.CustomerID;

DBA note: This works, but a LEFT JOIN + GROUP BY is often more scalable and easier to optimize.

3) When Joins Are the Best Tool

Use joins when you want columns from multiple tables in the same result set

If you want CustomerName and ServiceName alongside Booking details, a join expresses that clearly.

SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.Status,
    b.TotalAmountUSD,
    c.FullName   AS CustomerName,
    s.ServiceName
FROM dbo.Booking AS b
JOIN dbo.Customer AS c ON b.CustomerID = c.CustomerID
JOIN dbo.Service  AS s ON b.ServiceID  = s.ServiceID
ORDER BY b.BookingID;

Trying to do this with multiple scalar subqueries would be less readable and potentially slower.

Use joins when you need to aggregate across relationships

Example: total bookings per customer (including customers with zero bookings).

SELECT
    c.CustomerID,
    c.FullName,
    COUNT(b.BookingID) AS BookingCount
FROM dbo.Customer AS c
LEFT JOIN dbo.Booking AS b
    ON b.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.FullName
ORDER BY BookingCount DESC;

DBA advantage: The optimizer has a clear relational shape to work with.

4) When Subqueries Are the Best Tool

Use subqueries when you want “filter based on existence” (EXISTS / NOT EXISTS)

DBAs love EXISTS because it expresses intent clearly: “I only care whether at least one matching row exists.”

Example: customers who have at least one booking

SELECT
    c.CustomerID,
    c.FullName,
    c.Email
FROM dbo.Customer AS c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Booking AS b
    WHERE b.CustomerID = c.CustomerID
)
ORDER BY c.CustomerID;

Example: customers who have zero bookings (anti-join via NOT EXISTS)

SELECT
    c.CustomerID,
    c.FullName,
    c.Email
FROM dbo.Customer AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Booking AS b
    WHERE b.CustomerID = c.CustomerID
)
ORDER BY c.CustomerID;

DBA note: NOT EXISTS is usually safer than NOT IN because NOT IN can behave unexpectedly with NULLs.

Use subqueries when you need a single computed value to compare against

Example: bookings above average amount.

SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.TotalAmountUSD
FROM dbo.Booking AS b
WHERE b.TotalAmountUSD >
(
    SELECT AVG(TotalAmountUSD)
    FROM dbo.Booking
)
ORDER BY b.TotalAmountUSD DESC;

This reads naturally: “show bookings greater than the average booking amount.”

5) Comparing the Same Question: Join vs Subquery

Question: “List customers with their booking count.”

Option A: LEFT JOIN + GROUP BY (often preferred for scalability)

SELECT
    c.CustomerID,
    c.FullName,
    COUNT(b.BookingID) AS BookingCount
FROM dbo.Customer AS c
LEFT JOIN dbo.Booking AS b
    ON b.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.FullName
ORDER BY c.CustomerID;

Option B: Correlated subquery (clear, but can be slower on large data)

SELECT
    c.CustomerID,
    c.FullName,
    (SELECT COUNT(*) FROM dbo.Booking AS b WHERE b.CustomerID = c.CustomerID) AS BookingCount
FROM dbo.Customer AS c
ORDER BY c.CustomerID;

DBA guidance:

• For small tables, both can be fine.
• For large production tables, JOIN + GROUP BY often optimizes better and is easier to extend.
• If your intent is existence only (yes/no), EXISTS/NOT EXISTS is often perfect.

6) DBA Performance and Correctness Notes (Practical Rules)

Rule A: If you only need to know “does a match exist?”, use EXISTS

• EXISTS can stop at the first match.
• It avoids returning unnecessary columns.
• It avoids accidental row multiplication.

Rule B: If you need columns from both tables, use a JOIN

Joins are the natural relational tool for combining columns in one result.

Rule C: Avoid NOT IN unless you are 100% sure there are no NULLs

NOT IN with NULLs can produce “no rows” surprises.
DBAs generally prefer NOT EXISTS.

Rule D: Derived tables (subquery in FROM) are useful for staged logic

Sometimes you want to aggregate first, then join the aggregated result to another table.
That is a clean, DBA-friendly approach.

-- Aggregate payments per booking first, then join to bookings (staged approach)
SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.TotalAmountUSD,
    COALESCE(p.PayAttemptCount, 0) AS PaymentAttemptCount,
    COALESCE(p.AmountPaidUSD, 0)   AS AmountPaidUSD
FROM dbo.Booking AS b
LEFT JOIN
(
    SELECT
        BookingID,
        COUNT(*) AS PayAttemptCount,
        SUM(CASE WHEN PaymentStatus = 'SUCCEEDED' THEN AmountUSD ELSE 0 END) AS AmountPaidUSD
    FROM dbo.Payment
    GROUP BY BookingID
) AS p
    ON p.BookingID = b.BookingID
ORDER BY b.BookingID;

DBA benefit: This avoids row multiplication and keeps the logic clean.

7) Practice (Job-Ready Exercises)

Write and run each of these two ways (join approach and subquery approach) where applicable:

1. Customers who have at least one booking (EXISTS vs JOIN with DISTINCT).
2. Customers who have zero bookings (NOT EXISTS vs LEFT JOIN … IS NULL).
3. Bookings above the average TotalAmountUSD (subquery in WHERE).
4. Booking report with CustomerName and ServiceName (JOIN approach is the correct tool).
5. One row per booking including payment summary (derived table + join).

DBA check: For each query, answer:
“What is my output grain?” (one row per customer? per booking?) and “Could this multiply rows?”

Lesson Summary

• Use JOINs when you need columns from multiple tables or need grouped summaries across relationships.
• Use EXISTS/NOT EXISTS when you only care whether a related row exists (often clearer and safer).
• Correlated subqueries can be readable but may be slower at scale; JOIN + GROUP BY is often preferred.
• Derived tables (subquery in FROM) are excellent for “aggregate first, then join” patterns.
• Avoid NOT IN when NULLs might be present; prefer NOT EXISTS for anti-joins.

Next Lesson: INSERT / UPDATE / DELETE and Safe Patterns (Write Operations the DBA Way)

Next, you will learn how data changes happen: inserting new rows, updating existing rows, and deleting rows safely.
You will learn “DBA safe write patterns” that prevent accidental mass updates/deletes and keep changes controlled and auditable.