WHERE vs HAVING mental model in SQL - Hands-On Comparison

Understanding WHERE vs HAVING in SQL

📖 Scenario: You are managing a small bookstore's sales database. You want to analyze sales data to find out which books sold well and which did not. You will write SQL queries to filter data before and after grouping.

🎯 Goal: Build SQL queries that use WHERE and HAVING clauses correctly to filter rows before grouping and filter groups after aggregation.

📋 What You'll Learn

Create a table called sales with columns book_title (text), copies_sold (integer), and sale_date (date).

Insert 5 rows of sales data with specific values.

Write a query using WHERE to filter sales before grouping.

Write a query using HAVING to filter groups after aggregation.

💡 Why This Matters

🌍 Real World

Filtering sales data to analyze book performance by date and total copies sold is common in retail and business reporting.

💼 Career

Understanding WHERE vs HAVING is essential for writing correct SQL queries in data analysis, reporting, and database management roles.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns book_title (text), copies_sold (integer), and sale_date (date). Then insert these exact rows: ('The Alchemist', 10, '2024-01-10'), ('The Alchemist', 5, '2024-01-15'), ('1984', 8, '2024-01-12'), ('1984', 12, '2024-01-20'), ('Brave New World', 7, '2024-01-18').

SQL

-- Create the sales table and insert the 5 rows below
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows with exact values.

2

Filter rows with WHERE before grouping

Write a SQL query to select book_title and the total copies_sold as total_sold from sales. Use WHERE to include only rows where sale_date is before '2024-01-16'. Group the results by book_title.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

-- Write a query selecting book_title and SUM(copies_sold) as total_sold
-- Use WHERE to filter sale_date before '2024-01-16'
-- Group by book_title
-- Your code here

Hint

Use WHERE before GROUP BY to filter rows before grouping.

3

Filter groups with HAVING after aggregation

Write a SQL query to select book_title and the total copies_sold as total_sold from sales. Group by book_title. Use HAVING to include only groups where total_sold is greater than 15.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

SELECT book_title, SUM(copies_sold) AS total_sold
FROM sales
WHERE sale_date  15
-- Your code here

Hint

Use HAVING after GROUP BY to filter groups based on aggregate values.

4

Compare WHERE and HAVING in one query

Write a SQL query to select book_title and total copies_sold as total_sold from sales. Use WHERE to filter rows where sale_date is on or after '2024-01-12'. Group by book_title. Use HAVING to include only groups where total_sold is at least 15.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER,
  sale_date DATE
);

INSERT INTO sales (book_title, copies_sold, sale_date) VALUES
('The Alchemist', 10, '2024-01-10'),
('The Alchemist', 5, '2024-01-15'),
('1984', 8, '2024-01-12'),
('1984', 12, '2024-01-20'),
('Brave New World', 7, '2024-01-18');

SELECT book_title, SUM(copies_sold) AS total_sold
FROM sales
GROUP BY book_title
HAVING SUM(copies_sold) > 15;

-- Write a query selecting book_title and SUM(copies_sold) as total_sold
-- Use WHERE to filter sale_date >= '2024-01-12'
-- Group by book_title
-- Use HAVING to filter groups where total_sold >= 15
-- Your code here

Hint

Use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.

Practice

1. Which clause should you use to filter rows before grouping in a SQL query?

easy

A. HAVING

B. WHERE

C. GROUP BY

D. ORDER BY

Solution

1. Step 1: Understand filtering before grouping

   The WHERE clause filters individual rows before any grouping happens in the query.

2. Step 2: Compare WHERE and HAVING roles

   HAVING filters groups after grouping, so it cannot filter rows before grouping.

3. Final Answer:

   WHERE -> Option B

4. Quick Check:

   Filter rows before grouping = WHERE [OK]

Hint: Use WHERE for rows, HAVING for groups after grouping [OK]

Common Mistakes:

• Using HAVING to filter rows before grouping
• Confusing GROUP BY as a filter
• Using ORDER BY to filter data

2. Which of the following SQL queries correctly filters groups having a total sales greater than 1000?

easy

A. SELECT store, SUM(sales) FROM sales_data WHERE SUM(sales) > 1000 GROUP BY store;

B. SELECT store, SUM(sales) FROM sales_data WHERE sales > 1000 GROUP BY store;

C. SELECT store, SUM(sales) FROM sales_data GROUP BY store HAVING SUM(sales) > 1000;

D. SELECT store, SUM(sales) FROM sales_data HAVING SUM(sales) > 1000 GROUP BY store;

Solution

1. Step 1: Identify correct HAVING usage

   HAVING is used to filter groups based on aggregate functions like SUM.

2. Step 2: Check query syntax and order

   SELECT store, SUM(sales) FROM sales_data GROUP BY store HAVING SUM(sales) > 1000; correctly uses GROUP BY first, then HAVING with SUM(sales) > 1000.

3. Final Answer:

   SELECT store, SUM(sales) FROM sales_data GROUP BY store HAVING SUM(sales) > 1000; -> Option C

4. Quick Check:

   Filter groups by aggregate = HAVING [OK]

Hint: HAVING filters aggregates after GROUP BY [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Placing HAVING before GROUP BY
• Filtering rows instead of groups

3. Given the table orders(order_id, customer_id, amount), what will this query return?

SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE amount > 50 GROUP BY customer_id HAVING order_count > 2;

medium

A. Customers with total amount over 50

B. All customers with orders over 50 regardless of count

C. Syntax error because alias can't be used in HAVING

D. Customers with more than 2 orders where each order amount is over 50

Solution

1. Step 1: Understand alias usage in HAVING

   Many SQL databases allow using column aliases like order_count directly in HAVING clause, but standard SQL does not. However, most practical systems support it.

2. Step 2: Identify correct HAVING syntax

   Using alias in HAVING is often allowed; thus, the query returns customers with more than 2 orders where each order amount is over 50.

3. Final Answer:

   Customers with more than 2 orders where each order amount is over 50 -> Option D

4. Quick Check:

   HAVING filters groups; alias usage depends on SQL dialect [OK]

Hint: Use full aggregate in HAVING or alias depending on SQL dialect [OK]

Common Mistakes:

• Using alias in HAVING clause (may be allowed in some SQL dialects)
• Confusing WHERE and HAVING filters
• Assuming HAVING filters rows

4. Identify the error in this SQL query:

SELECT department, AVG(salary) FROM employees HAVING AVG(salary) > 50000 WHERE department LIKE 'Sales%' GROUP BY department;

medium

A. WHERE clause used after HAVING

B. HAVING clause used before GROUP BY

C. Missing alias for AVG(salary)

D. GROUP BY clause missing

Solution

1. Step 1: Check SQL clause order

   The correct order is WHERE, then GROUP BY, then HAVING.

2. Step 2: Identify misplaced WHERE clause

   In the query, WHERE appears after HAVING, which is invalid syntax.

3. Final Answer:

   WHERE clause used after HAVING -> Option A

4. Quick Check:

   WHERE before GROUP BY, HAVING after [OK]

Hint: WHERE before GROUP BY, HAVING after GROUP BY [OK]

Common Mistakes:

• Placing WHERE after HAVING
• Forgetting GROUP BY clause
• Using HAVING without GROUP BY

5. You want to find all customers who placed more than 3 orders with each order amount greater than 100. Which query correctly applies WHERE and HAVING?

hard

A. SELECT customer_id FROM orders WHERE amount > 100 GROUP BY customer_id HAVING COUNT(*) > 3;

B. SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 3 AND amount > 100;

C. SELECT customer_id FROM orders HAVING COUNT(*) > 3 WHERE amount > 100 GROUP BY customer_id;

D. SELECT customer_id FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id HAVING amount > 100;

Solution

1. Step 1: Filter rows with WHERE

   Use WHERE to keep only orders with amount > 100 before grouping.

2. Step 2: Filter groups with HAVING

   Use HAVING to keep customers with more than 3 such orders (COUNT(*) > 3).

3. Final Answer:

   SELECT customer_id FROM orders WHERE amount > 100 GROUP BY customer_id HAVING COUNT(*) > 3; -> Option A

4. Quick Check:

   WHERE filters rows, HAVING filters groups [OK]

Hint: WHERE filters rows, HAVING filters groups after grouping [OK]

Common Mistakes:

• Using HAVING to filter rows
• Placing WHERE after HAVING
• Using aggregate in WHERE clause