Query data using subqueries and APPLY
Subqueries and APPLY are powerful tools in SQL that can be used to retrieve data from multiple tables and join them together. Here’s an explanation of how to use subqueries and APPLY in SQL:
Subqueries: A subquery is a query that is nested inside another query. It is used to retrieve data from a single table or multiple tables that meet a specific condition. A subquery can be used in various clauses, such as SELECT, WHERE, and FROM. The result of the subquery is used by the outer query to retrieve data.
For example, consider the following query:
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2022-01-01');
In this query, the subquery retrieves customer_id values from the orders table where the order_date is greater than or equal to ‘2022-01-01’. The outer query then uses these customer_id values to retrieve names from the customers table.
APPLY: APPLY is an operator that allows you to join a table with a table-valued function. A table-valued function returns a table, which can be used to join with the other table. There are two types of APPLY operators: CROSS APPLY and OUTER APPLY.
CROSS APPLY returns only the rows from the left table that have a matching row in the right table. OUTER APPLY returns all the rows from the left table, even if there is no matching row in the right table.
For example, consider the following query:
SELECT *
FROM orders
OUTER APPLY (SELECT TOP 1 * FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer;
In this query, the APPLY operator is used to join the orders table with the customers table using the customer_id column. The TOP 1 clause ensures that only one row is returned from the customers table. The ORDER BY clause sorts the customers table by customer_name. The result of the query is a table that contains all the columns from the orders table and one additional column from the customers table.
Example of how to use subqueries and APPLY in Microsoft SQL Server:
Subqueries: Suppose we have two tables, orders and customers. The orders table contains information about orders placed by customers, and the customers table contains information about the customers. We can use a subquery to retrieve the names of customers who have placed orders in the last month.
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(MONTH, -1, GETDATE()));
In this query, the subquery retrieves customer_id values from the orders table where the order_date is greater than or equal to one month ago from the current date using the DATEADD and GETDATE functions. The outer query then uses these customer_id values to retrieve names from the customers table.
APPLY: Suppose we have the same two tables, orders and customers. We want to retrieve all orders and the corresponding customer information for each order. We can use OUTER APPLY to achieve this.
SELECT *
FROM orders
OUTER APPLY (SELECT TOP 1 * FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer;
In this query, the OUTER APPLY operator is used to join the orders table with the customers table using the customer_id column. The TOP 1 clause ensures that only one row is returned from the customers table. The ORDER BY clause sorts the customers table by customer_name. The result of the query is a table that contains all the columns from the orders table and one additional column from the customers table.
70-761 Exam Practice Questions
Q1. Which of the following queries retrieves all orders and the corresponding customer information for each order, including orders with no matching customer?
A.
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
B.
SELECT *
FROM orders
CROSS APPLY (SELECT TOP 1 * FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer;
C.
SELECT *
FROM orders
OUTER APPLY (SELECT TOP 1 * FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer;
D.
SELECT *
FROM orders
OUTER JOIN customers ON orders.customer_id = customers.customer_id;
Answer: c
Explanation: The OUTER APPLY operator is used to join the orders table with the customers table using the customer_id column, and the OUTER keyword ensures that all orders are included in the result set, even if there is no matching customer. The TOP 1 and ORDER BY clauses ensure that only one row is returned from the customers table and that it is sorted by customer_name.
Q2. Which of the following queries retrieves the names of customers who have not placed any orders?
A.
SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
B.
SELECT name
FROM customers
WHERE NOT EXISTS (SELECT customer_id FROM orders WHERE orders.customer_id = customers.customer_id);
C.
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(MONTH, -1, GETDATE()));
D.
SELECT name
FROM customers
WHERE customer_id = ALL (SELECT customer_id FROM orders);
Answer: b
Explanation: The NOT EXISTS operator is used to retrieve rows from the customers table where there is no matching row in the orders table. The subquery checks for customer_id values in the orders table that match the customer_id values in the customers table.
Q3. Which of the following queries retrieves the order IDs and corresponding customer names for orders placed in the last week?
A.
SELECT order_id, customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE order_date >= DATEADD(WEEK, -1, GETDATE());
B.
SELECT order_id, customer_name
FROM orders
OUTER APPLY (SELECT TOP 1 name AS customer_name FROM customers WHERE customer_id = orders.customer_id) AS customer
WHERE order_date >= DATEADD(WEEK, -1, GETDATE());
C.
SELECT order_id, customer_name
FROM orders
OUTER APPLY (SELECT TOP 1 name AS customer_name FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer
WHERE order_date >= DATEADD(WEEK, -1, GETDATE());
D.
SELECT order_id, customer_name
FROM orders
CROSS APPLY (SELECT TOP 1 name AS customer_name FROM customers WHERE customer_id = orders.customer_id ORDER BY customer_name) AS customer
WHERE order_date >= DATEADD(WEEK, -1, GETDATE());
Answer: c
Explanation: The OUTER APPLY operator is used to join the orders table with the customers table using the customer_id column, and the TOP 1 and ORDER BY clauses ensure that only one row is returned from the customers table and that it is sorted by customer_name. The WHERE clause filters the result set to include only orders placed in the last week.