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

Question #186609

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.


1
Expert's answer
2021-04-28T16:48:43-0400
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)

Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be the first!

Leave a comment

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS