Joins and Aggregates: GROUP BY, HAVING and Functions

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Beginner
Joins and Aggregates: GROUP BY, HAVING and Functions
Beginner Topic 2 of 5

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.

JoinRows returned
JOIN (inner)Only matches
LEFT JOINAll left rows plus matches
RIGHT JOINAll right rows plus matches
FULL JOINAll rows from both sides

Aggregate Functions

FunctionMeaning
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.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators