SQL JOINs

Medium

SQL JOINs combine rows from two or more tables based on a related column.

Why It Matters

JOIN is the single most important SQL concept. Every database interview and most placement SQL rounds test JOIN understanding — especially the difference between INNER, LEFT, RIGHT, and FULL OUTER.

How It Works

INNER JOIN: returns only rows with matching values in both tables. LEFT JOIN: returns ALL rows from the left table, with NULLs for non-matching right table rows. RIGHT JOIN: returns ALL rows from the right table, with NULLs for non-matching left table rows. FULL OUTER JOIN: returns ALL rows from both tables, NULLs where there's no match. CROSS JOIN: returns the Cartesian product (every combination). Always specify the JOIN condition with ON. Using WHERE instead of ON in outer joins can turn them into inner joins.

Example

SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; -- Returns ALL employees, even those with no department (dept_name = NULL for them)

Code Example

-- INNER JOIN: only matched rows
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: all orders, even if customer was deleted
SELECT * FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

-- Find customers with NO orders
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Common Mistakes

Confusing LEFT JOIN with INNER JOIN is the most common error. Another major trap: putting a filter on the right table in WHERE instead of in the ON clause — this silently converts a LEFT JOIN into an INNER JOIN.

Test Your Understanding

Easy Q1

What does a CROSS JOIN between a table with 3 rows and a table with 4 rows produce?

Easy Q2

Which join type preserves ALL rows from BOTH tables?

Hard Q3

What's wrong with this query? SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id WHERE d.location = 'Chennai';

Ready to Master SQL JOINs?

Practice with adaptive questions. Track your misconceptions. Build real mastery.

Free account. No credit card required.

Related Concepts

SQL GROUP BY and HAVING

GROUP BY collapses rows sharing common values into summary rows; HAVING filters these groups after aggregation.

SQL Subqueries

A subquery is a query nested inside another query, used to compute intermediate results for the outer query.

← All Sql Concepts Practice All Topics →