Mastering SQL Sub-queries : Learn by doing

When managing databases, the ability to craft efficient and powerful queries is crucial.

One advanced technique that adds a layer of sophistication to SQL queries is the use of sub-queries, also known as nested queries.

A sub-query is a query embedded within the WHERE clause of another query, commonly referred to as the main query.

This technique provides a way to filter data more precisely than with standard queries, offering a level of flexibility and complexity that can be invaluable in various scenarios.

Understanding Sub-queries

A sub-query is a SQL query nested within another query, serving as a building block to enhance the capabilities of the main query.

These sub-queries can be applied to SELECT, UPDATE, DELETE, and INSERT statements, enabling a wide range of applications.

The primary purpose of sub-queries is to filter or manipulate data in a way that is not achievable with a regular query.

Let’s delve into the various types of sub-queries and explore their applications through illustrative examples.

Given we have the following database tables in mysql database management system;

An Employees table containing information about employees, the Products table storing details about products, the Categories table classifying products into different categories, and the Orders table logging information about customer orders.

The relationships between these tables are established through primary and foreign keys.

Employees Table

• employee_id (Primary Key): Unique identifier for each employee.
• employee_name: The name of the employee.
• salary: The salary of the employee.

+ - - - - - - -+ - - - - - - - -+ - - - - - - - -+ - - - - +
| employee_id | employee_name | department_id | salary |
+ - - - - - - -+ - - - - - - - -+ - - - - - - - -+ - - - - +
| 1 | John Doe | 101 | 50000 |
| 2 | Jane Smith | 102 | 60000 |
| 3 | Mark Johnson | 101 | 55000 |
+ - - - - - - -+ - - - - - - - -+ - - - - - - - -+ - - - - +

Products Table

• product_id (Primary Key): Unique identifier for each product.
• product_name: The name of the product.
• category_id: Identifier for the category to which the product belongs.

+ - - - - - - + - - - - - - - - + - - - - - - -+
| product_id | product_name | category_id |
+ - - - - - - + - - - - - - - - + - - - - - - -+
| 101 | Laptop | 1 |
| 102 | Smartphone | 1 |
| 103 | T-shirt | 2 |
+ - - - - - - + - - - - - - - - + - - - - - - -+

Categories Table

• category_id (Primary Key): Unique identifier for each category.
• category_name: The name of the category.

+ - - - - - - -+ - - - - - - - -+
| category_id | category_name |
+ - - - - - - -+ - - - - - - - -+
| 1 | Electronics |
| 2 | Clothing |
| 3 | Furniture |
+ - - - - - - -+ - - - - - - - -+

Orders Table

• order_id (Primary Key): Unique identifier for each order.
• product_id (Foreign Key): References the product_id in the Products table.
• order_date: The date when the order was placed.

+ - - - - - + - - - - - - + - - - - - - +
| order_id | product_id | order_date |
+ - - - - - + - - - - - - + - - - - - - +
| 1001 | 101 | 2023–01–01 |
| 1002 | 102 | 2023–01–02 |
| 1003 | 103 | 2023–01–03 |
+ - - - - - + - - - - - - + - - - - - - +

SELECT Statement

Consider a scenario where you want to retrieve all employees who have a salary higher than the average salary in their respective departments.

SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) 
FROM employees e2 
WHERE e1.department_id = e2.department_id);

In this example, the sub-query calculates the average salary for each department, and the main query selects employees whose salary exceeds this departmental average.

+---------------+
| employee_name |
+---------------+
| Jane Smith    |
+---------------+

The main query selects employees whose salary is higher than the average salary in their respective departments.

In this case, only Jane Smith satisfies this condition.

Classifying Sub-queries

Single-Row Sub-query

This type of sub-query returns only one row of results.

It is commonly used in scenarios where a single value needs to be compared with the result of the sub-query.

SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

In this example, the sub-query retrieves the maximum salary from the employees table, and the main query selects the employee(s) with that salary.

Multiple-Row Sub-query

A multiple-row sub-query returns multiple rows of results.

It is employed when the main query needs to compare against a set of values.

SELECT product_name 
FROM products 
WHERE category_id 
IN (SELECT category_id 
FROM categories 
WHERE category_name = 'Electronics');

In this case, the sub-query fetches the category_id for the ‘Electronics’ category, and the main query selects all products belonging to that category.

Multiple-Column Sub-query

This type of sub-query returns multiple columns but only one row. It is used when the main query requires a set of values for a single row.

SELECT employee_name 
FROM employees 
WHERE (salary, department_id) = (SELECT MAX(salary), department_id FROM employees);

The sub-query here retrieves the maximum salary and its associated department_id, and the main query selects the employee(s) with the same salary and department.

Conclusion

Sub-queries offer a powerful tool for enhancing the precision and flexibility of SQL queries.

Whether used in SELECT, UPDATE, DELETE, or INSERT statements, sub-queries provide a means to manipulate and filter data in ways that standard queries cannot achieve.

Understanding the types of sub-queries and their practical applications empowers database developers to master this advanced SQL technique and optimize their database interactions.

As we’ve seen through examples, sub-queries open up a lot of possibilities, making them a valuable addition to the toolkit of any SQL practitioner.