Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Learn Subqueries in SELECT and WHERE Clauses | Subqueries in E-commerce Analytics
Subqueries in SQL

bookSubqueries in SELECT and WHERE Clauses


              
123456789

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

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.


              
12345678

            
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'
);
copy

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.

question mark

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

Select the correct answer

question-icon

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

SELECT name FROM products WHERE product_id ( SELECT product_id FROM orders );
Wireless Mouse
Bluetooth Headphones
Coffee Mug
Notebook
Water Bottle

Click or drag`n`drop items and fill in the blanks

Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 2

Ask AI

expand

Ask AI

ChatGPT

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how the second query works?

What is the difference between using a subquery in the SELECT clause versus the WHERE clause?

Can you show an example of using a JOIN instead of a subquery for this scenario?

bookSubqueries in SELECT and WHERE Clauses

Swipe to show menu


              
123456789

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

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.


              
12345678

            
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'
);
copy

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.

question mark

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

Select the correct answer

question-icon

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

SELECT name FROM products WHERE product_id ( SELECT product_id FROM orders );
Wireless Mouse
Bluetooth Headphones
Coffee Mug
Notebook
Water Bottle

Click or drag`n`drop items and fill in the blanks

Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 1. Chapter 2
some-alt