SQL Subqueries

What are SQL Subqueries?

A SQL subquery, also known as a nested query or inner query, is a query within another SQL statement. It allows you to use the result of one query as input in another query. Subqueries are enclosed in parentheses and can be used in various parts of a SQL statement, including SELECT, FROM, and WHERE clauses. They are a powerful tool for retrieving and manipulating data.

When you would use it

You would use SQL subqueries when you want to:

1. Retrieve data based on the result of another query: Use the output of one query as a filter or criteria for another query.

2. Simplify complex queries: Break down complex problems into smaller, more manageable subqueries to improve query readability and maintainability.

3. Perform aggregation within a query: Calculate aggregate functions on a subset of data, such as finding the average, sum, or maximum value.

4. Make comparisons: Compare values between tables or within the same table to find matching or non-matching records.

Syntax

The basic syntax for using a subquery in a SQL statement is as follows:

SELECT column1, column2, ...
FROM table
WHERE column operator (SELECT column FROM another_table WHERE condition);

• column1, column2, ...: The columns you want to retrieve.
• table: The main table from which you want to select data.
• column: The column you want to compare or filter against in the main query.
• operator: An operator such as =, >, <, IN, NOT IN, etc.
• another_table: The table from which you want to retrieve data for comparison.
• condition: The condition that specifies which records to select from the subquery.

Parameter values

• column1, column2, ...: A list of column names you want to retrieve.
• table: The name of the table from which you want to retrieve data.
• column: The name of the column you want to compare or filter in the main query.
• operator: An operator used to compare data, such as =, >, <, IN, NOT IN, etc.
• another_table: The name of the table used in the subquery.
• condition: The condition that specifies which records to select from the subquery.

Example query

Suppose you have a database with two tables, "employees" and "departments," and you want to retrieve the names of employees in the "Sales" department. You can use a subquery like this:

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

Example table response

Assuming the "employees" table contains the following data:

| employee_name  | department_id |
| -------------- | ------------ |
| John Smith     | 1            |
| Mary Johnson   | 2            |
| Sam Brown      | 1            |
| Alice White    | 3            |

And the "departments" table contains:

| department_id | department_name |
| ------------ | --------------- |
| 1            | Sales           |
| 2            | Marketing       |
| 3            | Finance         |

The query mentioned earlier would return the following result:

| employee_name  |
| -------------- |
| John Smith     |
| Sam Brown      |

This result is based on the subquery that finds the "department_id" for the "Sales" department and then filters employees in the main query based on that department ID.

Use cases

• Filtering data based on a condition derived from another query.
• Comparing values between tables.
• Simplifying complex queries by breaking them into smaller parts.
• Performing aggregation operations within a query.

SQL languages this is available for

SQL subqueries are a standard feature available in most relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of subqueries are consistent across these database systems, with minor variations in specific implementations.