Summary  
This chapter explains how to use subqueries in SELECT and WHERE clauses to filter results based on related table data or to include per-row calculated values within a single query.  

General domain of usage  
E-commerce analytics

A subquery in the `WHERE` clause lets you filter rows based on the results of another query. This is a powerful way to select only the data you need, using information from related tables.

**In e-commerce analytics, you can use a subquery in the WHERE clause to:**

- Find customers who have ordered products from a specific category;
- Identify products that have ever been purchased.

The subquery returns a set of values, and the main query includes only rows that match those values. This technique allows you to apply complex filters that depend on data from other tables.

**Key differences:**

- A subquery in the `WHERE` clause controls which rows appear in your results;
- A subquery in the `SELECT` clause adds calculated or related columns to each row.

Use subqueries in the WHERE clause whenever you need to filter data based on conditions that involve multiple tables or calculated results.

SELECT
    p.product_id,
    p.name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.product_id = p.product_id
    ) AS total_orders
FROM products p;

This query demonstrates how to use a subquery within the SELECT clause. The main query retrieves each product's ID and name from the `products` table. For each product, the subquery calculates the total number of orders by counting rows in the `orders` table where the `product_id` matches the current product. The result is a list of products, each with its total order count. This technique allows you to include calculated or aggregated data for each row in your result set, using subqueries that reference the current row's values.

SELECT DISTINCT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    WHERE p.category = 'Electronics'
);

Which statement best describes the difference between using a subquery in the SELECT clause and in the WHERE clause?

Master the art of writing and applying subqueries in SQL through real-world scenarios. This module covers the theory and practice of subqueries, including their use in SELECT, WHERE, and FROM clauses, as well as correlated subqueries. 

Explore subqueries using an e-commerce dataset, focusing on orders, customers, and products. Learn to extract insights and solve business problems using subqueries.

Subqueries in SELECT and WHERE Clauses

1. Which statement best describes the difference between using a subquery in the SELECT clause and in the WHERE clause?

2. Fill in the blanks to select product names from the `products` table that have been ordered at least once.

Subqueries in SELECT and WHERE Clauses

1. Which statement best describes the difference between using a subquery in the SELECT clause and in the WHERE clause?

2. Fill in the blanks to select product names from the products table that have been ordered at least once.

2. Fill in the blanks to select product names from the `products` table that have been ordered at least once.