Subqueries in SQL

A subquery in SQL is a query nested within another SQL query, commonly embedded in the WHERE clause. These subqueries are majorly utilized in SELECT, INSERT, UPDATE, DELETE, FROM clauses, functioning wherever an expression is permitted. The data retrieved by the subquery is employed by the encompassing statement as seamlessly as a literal value. Subqueries in SQL offer a streamlined and effective approach to manage queries contingent on the outcomes of another query. The implementation of a subquery in SQL must adhere to certain guidelines, which are delineated within this article to ensure proper usage and execution.

Subquery Rules

There are various rules that a subquery must follow, like:

• Subqueries must be enclosed within parenthesis. For example:
  In the above example, the query which is enclosed in the parenthesis is called a subquery.
• Subquery in sql always runs first followed by the main query.
• The subqueries in MySQL cannot use the ORDER BY keyword whereas the main query can use the ORDER BY keyword whenever required. You can use the GROUP BY command to perform the same function as the ORDER BY in the subquery.
• You cannot use BETWEEN within a SQL subquery. Although, it can be used with the main query.
• It is not possible for subqueries to be enclosed in a set of functions.
• Subquery in sql that returns multiple rows can only be used together with the numerous value operators. Such operators include the IN operator.
• The SELECT list must not include any references evaluating to a CLOB, NCLOB, BLOB, or ARRAY.
• When a subquery yields several rows at once, it should be paired exclusively with operators that handle multiple values, such as the IN operator.

Syntax

Key Points:

• Placement: Subqueries can be placed in the WHERE clause to specify conditions, the FROM clause to define a table source, or the SELECT clause to determine the values to be returned.
• Comparison Operators: Subqueries often use operators like =, >, <, IN, EXISTS, ANY, ALL, or NOT IN to filter results based on the subquery's output.
• Single-row vs. Multi-row: Single-row subqueries return a single value, using operators like = or <>. Multi-row subqueries, on the other hand, return multiple rows and require operators like IN, ANY, or ALL.
• Correlated Subqueries: These are subqueries that refer to columns in the outer query, executed once for each row processed by the outer query.

Sample Table DB

To understand the subqueries with the different keywords in SQL, the following tables are used as a reference throughout the article.

Employee:

EmployeeNew:

Department:

Subqueries with SELECT Statement

In most cases, subqueries are used with the SELECT statements.

Syntax:

To understand more clearly let's take the above mentioned Employee table and run the below mentioned query and observe the output.

Example:

The final result of the above query is:

Subqueries with INSERT statement

Following the SELECT statement, utilizing subqueries within INSERT statements stands as the next most effective choice. The data fetched by the subquery can then be manipulated using various character functions, numerical operations, and more.

Syntax:

Example:

The final result of the above query i.e. the EmployeeNew table would look like below:

EmployeeNew:

Subqueries with UPDATE Statement

The UPDATE statement is used to modify an existing record within the table. To benefit from the usage of subqueries the WHERE clause should be used with the UPDATE otherwise the overall functioning of the table may get disturbed. In the UPDATE statement, the two clauses in which subqueries are most commonly used are SET and WHERE.

Syntax:

Example:

The final result of the above query would look like the below:

Employee:

Subqueries with DELETE Statement

The subqueries can also be used with the DELETE statements to delete one or multiple records from the table.

Syntax:

Example:

The final result of the above query i.e. the Employee table would look like the below:

Subqueries in FROM clause

FROM is used to specify the source from which the data is to be fetched. Here, subqueries in FROM clause create an intermediate table that can be used directly to retrieve the results for the main SELECT query or joined with the other tables and then used subsequently.

Example:

The final result of the above query would look like below:

Subqueries in WHERE Clause

To filter the rows from the result set, subqueries in the WHERE clause can be used.

Syntax:

Example:

The final result of the above query is:

Subqueries and UNIONs

Subqueries and UNION operators in SQL are powerful tools that allow for complex data retrieval from multiple tables. Subqueries enable you to perform operations that filter, aggregate, or manipulate data based on conditions applied to a subset of data. The UNION operator, on the other hand, is used to combine the result sets of two or more SELECT statements into a single result set, eliminating duplicate rows by default.

Subqueries Example:

Consider a scenario where you want to find the names of employees who earn more than the average salary in their respective departments. You can use a subquery to calculate the average salary per department and then use this information in the outer query to filter the employees.

Output Table:

UNION Example:

Suppose you want to create a list of all unique department names from both the Employee and Department tables. You can achieve this by using the UNION operator to combine and deduplicate the department names.

Output Table: