SQL JOIN

A JOIN combines rows from two tables wherever a condition matches — here, users and their orders on users.id = orders.user_id.

LEFT JOIN

users

idnamecity
1AshaPune
2RaviMumbai
3NehaPune
4KiranDelhi

orders

iduser_idamountstatus
10111200paid
1021800paid
10321500pending
1043500paid
1055999paid

result

nameorder_idamount
no rows yet

Compare every user with every order on users.id = orders.user_id.

Step 1 / 27
Result rows
0
SELECT u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
Join typeKiran has no orders; order 105 belongs to no user — watch what each join does with them.

What's happening?

  1. Each user row is compared with each order row; a match adds a combined row to the result.
  2. INNER keeps only matches. LEFT also keeps users with no orders, filling the order columns with NULL.
  3. RIGHT keeps every order, including one whose user does not exist; FULL keeps unmatched rows from both sides.

Complexity

Time
O(n × m) compared row by row as shown; databases use hash or merge joins and indexes to do far less
Space
O(result rows)

Where you'll meet it

Orders with customer names, posts with authors, enrolments with courses — almost every screen in a business app is a JOIN.

Common mistake

Putting a filter on the right table in WHERE after a LEFT JOIN: it removes the NULL rows and quietly turns it into an INNER JOIN.

FAQ

What is the difference between INNER and LEFT JOIN?

INNER returns only rows that match on both sides; LEFT returns every row of the left table, with NULLs where nothing matched.

Why do some rows show NULL?

An outer join keeps a row that had no partner, and fills the missing side with NULL.

Does the database really compare every pair?

Not usually. With an index or a hash join it finds matches without checking every pair; the step-by-step view shows what the result means.