Why Joins?
Data is split into tables so nothing is stored twice. A join puts the pieces back together. Our orders table stores only customer ids, so we join to customers to see names.
INNER JOIN
Returns only rows that match in both tables.
sql
SELECT c.full_name, p.product_name, o.quantity, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
ORDER BY c.full_name;
LEFT JOIN
Returns all rows from the left table, even if there is no match. Missing values show as NULL.
sql
SELECT c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.full_name;
Result
Evan Brooks appears with a NULL order_id because he has no orders. An inner join would hide him.
| Join | Rows returned |
JOIN (inner) | Only matches |
LEFT JOIN | All left rows plus matches |
RIGHT JOIN | All right rows plus matches |
FULL JOIN | All rows from both sides |
Aggregate Functions
| Function | Meaning |
COUNT(*) | Number of rows |
SUM(x) | Total |
AVG(x) | Average |
MIN(x) / MAX(x) | Smallest / largest |
sql
SELECT COUNT(*) AS total_orders,
SUM(quantity) AS total_items
FROM orders;
GROUP BY
Groups rows first, then calculates per group.
sql
SELECT status, COUNT(*) AS orders_count
FROM orders
GROUP BY status;
HAVING: Filter the Groups
WHERE filters rows before grouping. HAVING filters groups after grouping.
sql
SELECT c.city, SUM(o.quantity * p.price) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
WHERE o.status <> 'CANCELLED'
GROUP BY c.city
HAVING SUM(o.quantity * p.price) > 500
ORDER BY revenue DESC;
Rule: Every column in SELECT that is not inside an aggregate function must appear in GROUP BY. Oracle raises ORA-00979 if you forget.
Useful Single-Row Functions
sql
SELECT UPPER(full_name) AS name_upper,
NVL(city, 'Unknown') AS city,
TO_CHAR(created_on,'DD-MON-YYYY') AS joined
FROM customers;
Conclusion
Joins connect tables, aggregates summarize them, GROUP BY makes one result per group and HAVING filters the groups.
Quick Summary: WHERE filters rows, HAVING filters groups. Use LEFT JOIN to keep rows with no match.