nerdexam
CompTIA

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.

Data Analysis

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)
  • A
    71% (32)
  • B
    9% (4)
  • C
    16% (7)
  • D
    4% (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.

AA SELECT * FROM CUSTOMERS AS C LEFT JOIN PRODUCTS AS P ON C.ID =Correct

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.

BSELECT * FROM CUSTOMERS AS C INNER JOIN PRODUCTS AS P ON C.ID = C.IDWHERE

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.

CSELECT * FROM PRODUCTS AS P INNER JOIN CUSTOMERS AS C ON P.Customer_ID = C.ID

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.

DSELECT * FROM PRODUCTS AS P LEFT JOIN CUSTOMERS AS C ON P.Customer_ID = C.ID

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

#SQL#LEFT JOIN#Database queries#Relational databases

Community Discussion

No community discussion yet for this question.

Full DA0-002 Practice