The Basics of writing Subqueries in SQL.

To newbies like myself and others weary and intimidated by coding, subqueries in SQL might seem like a dead-end. Whichever way you look at it SQL subqueries shouldn’t take forever to learn and master.

In this article, I share with you simple and basic steps to writing perfect subqueries that are yet useful for querying your database. I believe it is important to keep it simple when writing subqueries.

source: freecodecamp.org

What is Subquery ?

In simple terms, a subquery is a query inside another query. It’s a way of asking more than one question at the same time about data in your database. Something like using the answer from a prior question to ask another question.

Think of it like this, you wish to know the list of top goal scorers and at this time the number of goals scored by the individual footballer in a particular football season. First, you might want to ask a question like "Who are the top goal scorers ?" to get a list of the footballers. Then, you might want to ask a second question "How many goals did each of them score?"

In the same way, a subquery lets you ask a question about your data, and then use the answer to ask another question. For example, you might use a subquery to find all the customers who have made a purchase in the past month, and then use that list to ask a second question about what products they bought.

Subqueries are important because they allow you to break down complex questions into smaller, more manageable pieces. This makes it easier to write complex queries, and also helps to improve the performance of your database by reducing the amount of data that needs to be processed.

There are three types of subqueries in SQL: scalar subqueries, single-row subqueries, and multiple-row subqueries. Let’s explore each of them in more detail.

Scalar Subqueries

A scalar subquery is a subquery that returns a single value. It can be used in any part of a query where a single value is expected, such as in the SELECT clause, WHERE clause, or HAVING clause.

a.) Here’s an example of a scalar subquery in the WHERE clause to retrieve all orders with a total amount greater than the average total amount for all orders:

SELECT *
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

In this example, the subquery (SELECT AVG(total_amount) FROM orders) returns a single value, which is the average total amount from the orders table. This value is then used in the WHERE clause to filter out all orders with a total amount greater than the average.

b) Retrieve the customer with the highest credit limit:

SELECT *
FROM customers
WHERE credit_limit = (SELECT MAX(credit_limit) FROM customers);

Single-Row Subqueries:

A single-row subquery is a subquery that returns one or more rows, but only one row is expected. Single-row subqueries are commonly used to retrieve a single value from a table based on some condition. Here’s an example:

SELECT customer_name, 
    (SELECT MAX(order_date) 
      FROM orders
      WHERE customer_id = customers.customer_id) AS last_order_date
FROM customers;

In this example, the subquery (SELECT MAX(order_date) FROM orders WHERE customer_id = customers.customer_id) returns the maximum order date for a specific customer. The subquery is used in the SELECT clause to retrieve the last order date for each customer.

b.) Retrieve the product with the minimum unit price:

SELECT *
FROM products
WHERE unit_price = (SELECT MIN(unit_price) 
FROM products);

Multiple-Row Subqueries:

A multiple-row subquery is a subquery that returns one or more rows, and multiple rows are expected. Multiple-row subqueries are commonly used with the IN or EXISTS operators to check whether a value exists in a table. Here’s an example:

a) Retrieve all customers who have placed an order:

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

In this example, the subquery (SELECT customer_id FROM orders) returns a list of customer IDs from the orders table. The main query then uses the IN operator to retrieve all customers whose IDs are in that list.

b) Retrieve all orders for products that are currently out of stock:

SELECT *
FROM orders
WHERE product_id IN 
(SELECT product_id 
FROM products 
WHERE units_in_stock = 0);

These are the three types of subqueries in SQL. By using subqueries, you can write more complex queries that can handle a wider range of data requirements.

These are just a few examples of how subqueries can be used in SQL. By nesting queries within each other, you can create more complex and powerful queries to retrieve and manipulate your data.