Unlocking SQL: Joins, Aggregations & Subqueries

6/13/2025

Deep dive into SQL joins, powerful aggregation functions, and versatile subqueries—key tools for intermediate to advanced developers working with relational data.

What You’ll Learn

• Core SQL JOIN types with clear examples
• How to leverage aggregate functions and GROUP BY
• Effective usage of subqueries to filter, transform, and derive data
• Real-world workflows combining joins, aggregation, and subqueries

1. Understanding SQL JOINs

Relational databases store normalized data, often across related tables. JOINS combine this data based on defined relationships.

1SELECT e.id, e.name, d.department_name
2FROM employees e
3INNER JOIN departments d ON e.department_id = d.id;

• INNER JOIN: Returns only matching rows
• LEFT JOIN: All left rows, with matched right rows
• RIGHT JOIN: All right rows, with matched left rows
• FULL OUTER JOIN (where supported): All rows, matched when possible

Each join handles NULLs and missing data differently—be mindful of the results.

2. Aggregation: SUM, AVG, COUNT & More

Aggregate functions summarize groups of rows:

1SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
2FROM employees
3GROUP BY department_id
4HAVING AVG(salary) > 80000;

• COUNT(), SUM(), AVG(), MIN(), MAX()
• Use GROUP BY to group rows logically
• Optional HAVING filters groups post-aggregation
• Don’t mix aggregated and non-aggregated columns without grouping

3. Using Subqueries

Subqueries are nested SELECT queries used in various clauses:

1. In WHERE:

1SELECT name FROM departments WHERE id IN (
2  SELECT department_id FROM employees WHERE salary > 100000
3);

2. Correlated subqueries (depend on outer query):

1SELECT e.name, e.salary
2FROM employees e
3WHERE e.salary > (
4  SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
5);

3. In FROM clause: create derived tables

1SELECT dept, avg_salary FROM (
2  SELECT department_id AS dept, AVG(salary) AS avg_salary
3  FROM employees
4  GROUP BY department_id
5) AS dept_stats
6WHERE avg_salary > 90000;

4. Real-World Example: Sales Analysis

You’re tasked with reporting on your company’s monthly sales performance by region.

Step A: Join Tables

1SELECT s.order_id, c.region, p.category, s.amount
2FROM sales s
3JOIN customers c ON s.customer_id = c.id
4JOIN products p ON s.product_id = p.id;

Step B: Aggregate by Region

1SELECT c.region,
2       COUNT(*) AS total_orders,
3       SUM(s.amount) AS total_revenue
4FROM sales s
5JOIN customers c ON s.customer_id = c.id
6GROUP BY c.region;

Step C: Identify High-Performing Regions

1WITH region_stats AS (
2  SELECT c.region,
3         SUM(s.amount) AS total_revenue
4  FROM sales s
5  JOIN customers c ON s.customer_id = c.id
6  GROUP BY c.region
7)
8SELECT region, total_revenue
9FROM region_stats
10WHERE total_revenue > (
11  SELECT AVG(total_revenue) FROM region_stats
12);

• Uses joins, aggregation, CTE, and a subquery together
• Common in dashboards, reporting, and analytical SQL

5. Quick Exercises

• Join orders, customers, products and select relevant fields
• Aggregate total, average, and max order values per customer
• Find customers whose average order exceeds site-wide average
• Use a CTE and subquery to filter top regions for sales

Try them in your favorite SQL environment, then iterate by adding filters or indexes.

FAQs

Q1: When should I use JOINS instead of subqueries?

Joins generally perform better and are clearer when combining multi-table data. Subqueries are best for filtering derived values.

Q2: Can I use aggregation with a subquery?

Yes—e.g., WHERE salary > (SELECT AVG(salary) FROM ...).

Q3: What’s a correlated subquery?

A subquery referencing the outer query’s row context—executed per row.

Q4: When is a CTE preferred?

For readability and reusability—CTEs can simplify nested queries.

Q5: Do all databases support FULL OUTER JOIN?

No—MySQL doesn’t. You need a UNION of LEFT and RIGHT JOIN.

Q6: Can aggregates be in SELECT without GROUP BY?

Only if no non-aggregated fields exist. Otherwise, grouping is required.

Conclusion

This part equips you with essential tools to cut across relational data using JOINS, calculate insights with aggregation, and add flexibility with subqueries and CTEs. You’re now ready to build analytical reports and dashboards built on your database.

---

Land Your Next $100k Job with Ladders

---