Interview Forge

DATABASES / SYSTEM CONCEPT BRIEF

SQL joins

A join combines rows from two tables based on a related column, typically a foreign key.

BeginnerPhase 04 / Topic 10 of 16RequirementsTrade-offsFailure modes
01

Overview

A join combines rows from two tables based on a related column, typically a foreign key. INNER JOIN returns only rows that match in both tables; LEFT JOIN returns every row of the left table with matches or NULLs; RIGHT and FULL OUTER joins are the mirror and the union; CROSS JOIN returns every combination.

Joins are what make normalized schemas practical: data lives in one place, and queries assemble it. At scale, joins are also where performance problems appear, so you should know how join type, indexes on join keys, and row counts affect cost, and when to denormalize instead.

Matching guest list and RSVP cards

INNER JOIN lists guests who sent an RSVP. LEFT JOIN lists every invited guest, with a blank if they did not reply. FULL OUTER JOIN also includes RSVP cards from people who were never invited.

02

When to use it

  • Combining normalized data (orders with customers).
  • Finding missing relationships (customers with no orders).
  • Reporting and analytics across entities.
  • Deciding when denormalization is justified.
03

Where it shows up in interviews

Relational queries

Recognize it when: combine entities for a page or report.

  • Show a user's orders with product names
  • Top customers by revenue
Anti-join

Recognize it when: find records without a related record.

  • Users who never purchased
  • Products with no reviews
04

Where it is used in real software

ORMs

Hibernate, Prisma, and ActiveRecord generate joins; eager loading prevents N+1 query problems.

Data warehouses

Star schemas join a large fact table (sales) with dimension tables (date, product, store).

Sharded systems

Cross-shard joins are expensive, so sharded designs co-locate related data by the same shard key.

05

Key terms

INNER JOIN
Only matching rows from both sides.
LEFT JOIN
All left rows; NULLs where no right match.
FULL OUTER JOIN
All rows from both sides.
Anti-join
LEFT JOIN ... WHERE right.id IS NULL, or NOT EXISTS.
Self join
Joining a table to itself (employee and manager).
06

How it works, step by step

  1. 1
    Identify the relationship

    orders.customer_id references customers.id.

  2. 2
    Choose the join type

    Do you need unmatched rows? LEFT. Only matches? INNER.

  3. 3
    Index the join keys

    Especially the foreign key side.

  4. 4
    Filter early

    WHERE clauses reduce rows before expensive joins.

  5. 5
    Check row multiplication

    Joining one-to-many relationships can duplicate rows; aggregate carefully.

07

Join types on a small dataset

customers: Ana (1), Bo (2), Cy (3). orders: #10 by 1, #11 by 1, #12 by 4 (unknown customer)

Step 1 / 5
JoinRows returned
INNER JOINAna-#10, Ana-#11
LEFT JOIN (customers left)Ana-#10, Ana-#11, Bo-NULL, Cy-NULL
RIGHT JOIN (orders right)Ana-#10, Ana-#11, NULL-#12
FULL OUTER JOINAna-#10, Ana-#11, Bo-NULL, Cy-NULL, NULL-#12
Anti-join (customers without orders)Bo, Cy

NOWJoin: INNER JOIN | Rows returned: Ana-#10, Ana-#11

Order #12 references a missing customer, which a foreign key constraint would have prevented.

08

Implementation

-- Orders with customer names (only matches)SELECT o.id, c.name, o.total_centsFROM orders oJOIN customers c ON c.id = o.customer_id; -- All customers with their order count, including zeroSELECT c.name, count(o.id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id, c.name; -- Customers who never ordered (anti-join)SELECT c.name FROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); -- Beware row multiplication: joining items AND payments to orders duplicates rowsSELECT o.id,       (SELECT sum(qty) FROM order_items i WHERE i.order_id = o.id) AS items,       (SELECT sum(amount) FROM payments p WHERE p.order_id = o.id) AS paidFROM orders o;
09

Complexity and performance

Indexed join~O(n log m)

Nested loop with index.

Hash joinO(n + m)

Memory for the smaller side.

10

Trade-offs

Normalize and join vs denormalize

Joins keep one source of truth; denormalizing speeds reads but duplicates data and complicates writes.

Joins in the DB vs in application code

Databases join efficiently with indexes; application-side joins across services cost network round trips.

11

Variants and related techniques

LATERAL joins

Run a subquery per row, for example 'latest 3 orders per customer'.

Semi-join

EXISTS returns left rows that have at least one match, without duplicates.

12

Common mistakes

  • Filtering the right table in WHERE after a LEFT JOIN.

    Fix: WHERE o.status = 'paid' turns it into an inner join; put the condition in the ON clause.

  • Row explosion from multiple one-to-many joins.

    Fix: Aggregate in subqueries before joining.

  • Missing index on the foreign key.

    Fix: Index orders.customer_id.

13

Interview questions

What is the difference between INNER and LEFT JOIN?

INNER returns only rows with matches in both tables. LEFT returns every row from the left table, with NULLs for columns of the right table where no match exists.

How do you avoid cross-shard joins in a sharded database?

Shard related tables by the same key (for example tenant_id or user_id) so joins stay within one shard, and denormalize or precompute data needed across shards.

14

Practice problems

ProblemDifficultyWhat it trains
Write queries for each join typeEasySemantics.
Latest order per customerMediumLATERAL or window functions.