How do you use JOINs with aggregations and conditions in MySQL?
Using JOINs with Aggregations and Conditions in MySQL
JOINs can be combined with aggregate functions (like COUNT, SUM, AVG) and conditions (WHERE, HAVING) to summarize or filter data across related tables.
- 1
• Aggregates are often used to compute totals, averages, or counts grouped by certain columns.
- 2
• JOINs bring together the necessary data from multiple tables before aggregation.
- 3
• Use GROUP BY to specify how rows should be grouped for aggregation.
In this example, the customers table is joined with orders to count how many orders each customer has. LEFT JOIN ensures customers with no orders are included with a count of zero.
- 1
• Use HAVING to filter results after aggregation, unlike WHERE which filters before aggregation.
- 2
• Example: Only show customers with more than 5 orders.
- 1
• Always GROUP BY columns from the non-aggregated table to avoid errors.
- 2
• JOINs can bring in additional columns needed for grouping or filtering.
- 3
• Proper indexing on join columns improves performance, especially with large tables.
- 4
• Combine WHERE for pre-aggregation filtering and HAVING for post-aggregation conditions.
In summary: Using JOINs with aggregations and conditions allows you to summarize and filter related data efficiently. Proper use of GROUP BY, HAVING, and indexing ensures accurate and performant queries.
Scenario Questions
0-2 years experience
- 1We have tables orders(order_id, customer_id, amount) and customers(customer_id, country). Write a query to list each country with the total order amount, but only include countries where the total exceeds $10,000.
- 2How would you retrieve the most recent order per customer together with the total number of orders that customer placed, using a JOIN and aggregation?
2-5 years experience
- 1Our reporting feature groups sales by product category, but we need to filter out categories where the average discount is below 5% after joining with the discounts table. The query is returning wrong results—what might be wrong with where you placed the condition?
- 2During a code review you see a query that joins a large transactions table with users, then groups by user_id, but the HAVING clause references a column from the joined table. Explain the performance impact and how you would rewrite it.
5-8 years experience
- 1Our nightly analytics run on a 500 M‑row orders table joined with a 50 M‑row products table, then aggregates by product category. Discuss indexing and query‑rewrite techniques to keep the job under 30 minutes.
- 2We are considering a materialized view to store pre‑aggregated sales per region to avoid heavy joins. What trade‑offs should we evaluate, and how would you keep the view consistent with source tables?
8+ years experience
- 1The company plans to migrate from MySQL to a distributed analytics platform. How would you redesign the existing join‑aggregation queries to minimize data movement and support cross‑team reporting?
- 2Multiple teams maintain overlapping denormalized tables for performance. Propose a governance and schema strategy that balances query speed with data consistency when using joins with aggregations across services.
Follow-up Questions
- What changes when you move a filter from WHERE to HAVING?
- How do you check that the optimizer is using your intended indexes?
- When might a LEFT JOIN with aggregation produce unexpected duplicate rows?