Querying Microsoft SQL Server 2012/2014 — Question 1

You have a Microsoft SQL Server database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
✑ Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
✑ Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?

Answer options

• A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• B. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
• D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Correct answer: D

Explanation

The correct answer is D because a LEFT OUTER JOIN will return all records from the Customers table and the matching records from the Orders table, including customers with no orders (NULL values for OrderDate). Option A uses a RIGHT OUTER JOIN, which would not fulfill the requirement to include all customers. Option B is an INNER JOIN, which excludes customers without orders. Option C is a CROSS JOIN, resulting in a Cartesian product, which is not appropriate for this requirement.