In this medium post, we will aim to discuss best practices and considerations using Subqueries.
Learning Objectives:
- Discuss how to write subqueries within subqueries.
- Discuss performance limitations.
- Explain how to use subqueries as calculations.
- Describe best practices using subqueries.
Best Practices with Subqueries.
You can essentially have an infinite amount of nested subqueries at the expense of performance. Subquery selects can only retrieve a single column too.
Subquery in Subquery.
SELECT customer_name, customer_contact
FROM customers
WHERE customer_id IN
SELECT customer_id
FROM orders
WHERE order_number IN (SELECT order_number
FROM order_items
WHERE prod_name = 'Toothbrush');Notice that this query is indented so that it is easy to read and understand.
SELECT customer_name, customer_contact
FROM customers
WHERE customer_id IN
SELECT customer_id
FROM orders
WHERE order_number IN (SELECT order_number
FROM order_items
WHERE prod_name = 'Toothbrush');This is the same query but with no indentation so it is harder to read. Think of indentation like you would a paragraph in an essay. Important to have so that the reader can understand the structure and different parts of your essay.
PoorSQL Website.
This website will format code for you, it used proper indentation and the code is easier to read and troubleshoot.
Subqueries for Calculation.
You can use subqueries as calculations too. Take a look at the example below:
SELECT COUNT (*) AS orders
FROM Orders
WHERE customer_id = '123456';
SELECT customer_name,
customer_state,
(SELECT (*) AS orders
FROM Orders
WHERE Orders.customer_id =
Customer.customer_id) AS orders
FROM customers
ORDER BY customer_nameIn this calculation, we want to get the customer name, region and the total number of orders.
We could count the orders for a customer id but then we still would not have the customer’s name or region.
That is why, in this case, we use a subquery.
The Power of Subqueries.
They are powerful tools but not always the best option because of their performance.
