DA0-002 · Question #70
A data analyst wants to use the following tables to find all the customers who have not placed an order: Which of the following SQL statements is the best way to accomplish this task?
The correct answer is A. A SELECT * FROM CUSTOMERS AS C LEFT JOIN PRODUCTS AS P ON C.ID =. To find customers who have not placed an order, a LEFT JOIN from the CUSTOMERS table to the PRODUCTS (or orders) table is used. The query then filters for rows where the columns from the PRODUCTS table are NULL, indicating no matching orders for those customers.
Question
A data analyst wants to use the following tables to find all the customers who have not placed an order:
Which of the following SQL statements is the best way to accomplish this task?
Options
- AA SELECT * FROM CUSTOMERS AS C LEFT JOIN PRODUCTS AS P ON C.ID =
- BSELECT * FROM CUSTOMERS AS C INNER JOIN PRODUCTS AS P ON C.ID = C.IDWHERE
- CSELECT * FROM PRODUCTS AS P INNER JOIN CUSTOMERS AS C ON P.Customer_ID = C.ID
- DSELECT * FROM PRODUCTS AS P LEFT JOIN CUSTOMERS AS C ON P.Customer_ID = C.ID
How the community answered
(45 responses)- A71% (32)
- B9% (4)
- C16% (7)
- D4% (2)
Why each option
To find customers who have not placed an order, a `LEFT JOIN` from the `CUSTOMERS` table to the `PRODUCTS` (or orders) table is used. The query then filters for rows where the columns from the `PRODUCTS` table are `NULL`, indicating no matching orders for those customers.
The `LEFT JOIN` from the `CUSTOMERS` table to the `PRODUCTS` table includes all customers, even those without a matching entry in the `PRODUCTS` table (representing orders). By adding `WHERE P.Customer_ID IS NULL` (assuming `P.Customer_ID` is the join column on the `PRODUCTS` side), the query specifically filters for customers who have no corresponding orders, thus identifying those who have not placed an order.
An `INNER JOIN` would only return customers who have placed an order (i.e., have matching entries in both tables), which is the opposite of the desired result. The join condition `C.ID = C.ID` is also incorrect.
Similar to choice B, an `INNER JOIN` only returns customers with orders. Additionally, starting the `LEFT JOIN` from `PRODUCTS` and then joining to `CUSTOMERS` would not correctly identify customers without orders.
A `LEFT JOIN` from the `PRODUCTS` table to `CUSTOMERS` would list all products and their associated customers, but it would not identify customers who have no orders; it would show products that might not have a customer or all products and their customers.
Concept tested: SQL LEFT JOIN for unmatched records
Source: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-clause-transact-sql#left-outer-join
Topics
Community Discussion
No community discussion yet for this question.