Answer to Question #186609 in Databases | SQL | Oracle | MS Access for Reena Dhingra

Question

Write separate queries using a join, a subquery, a CTE, and then an EXISTS to list all AdventureWorks customers who have not placed an order.

Answer 1

Using JOIN:
SELECT *
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
WHERE s.SalesOrderID IS NULL

Using CTE:
WITH s AS
(   SELECT SalesOrderID
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM Sales.Customer c
LEFT OUTER JOIN s ON c.customerID = s.customerID
WHERE s.SalesOrderID IS NULL

Using SubQuery:
SELECT *
FROM Sales.Customer c
where c.CustomerID in(
SELECT s.CustomerID
FROM Sales.SalesOrderHeader s
WHERE s.SalesOrderID IS NULL)

Using EXISTS:
SELECT *
FROM Sales.Customer c
where EXISTS(
SELECT *
FROM Sales.SalesOrderHeader s
WHERE s.SalesOrderID IS NULL
AND c.customerID = s.customerID)