MySQL Database Management

Chapter 6: Advanced Queries - JOINs, Subqueries

6.1 INNER JOIN

INNER JOIN returns rows that have matching values in both tables.

SELECT users.username, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

6.2 LEFT JOIN and RIGHT JOIN

LEFT JOIN returns all rows from left table, matching rows from right.

SELECT users.username, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

6.3 Subqueries

Subqueries are queries nested inside other queries.

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Subquery in SELECT
SELECT username, 
       (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
FROM users;