Subqueries in SQL

The queries are used inside the other query is known as the Subqueries. The subquery is also called as Inner query or Nested query. The subquery is used when we want to perform some operations based on the result of another query.

The query that represents the parent query is called an outer query or main query. The subqueries are mostly inserted in the WHERE statement. The subqueries are used to filter the data present in the table.

Guidelines to use Subqueries:

  • Subquery should be created in parentheses.
  • Subqueries should be placed on the right side of the operator.
  • ORDER BY statement should not be used to a subquery.
  • Use single-row and multi-row operators with their respective operators.

Syntax of the subquery :

//parent query
SELECT column_name_1, column_name_2......
FROM table_name
WHERE condition<operator> (//subquery
                  SELECT column_name_1, column_name_2......
                  FROM table_name
                  WHERE condition);

Example of the subquery :

SELECT first_name, last_name, salary, city
FROM persons_table
WHERE salary = (SELECT MAX (salary)
                FROM persons_table);

Let us divide this query and see how it works.
subquery

This is the subquery of the above query.

SELECT MAX (salary)
FROM persons_table;

In this query, we need to know the maximum salary value from the persons_table.
subquery-eg

This is the outer query or the parent query.

SELECT first_name, last_name, salary, city
FROM persons_table
WHERE salary =55000;

After we get the value of the maximum salary of a person, we need to retrieve the required columns. i.e, first_name, last_name, salary, and the city. These columns are retrieved by using the SELECT statement.
subqyery-eg-1

Types of Subqueries in SQL

There are three types of subqueries. They are as follows.

  • Single-row subquery
  • Multi-row subquery
  • Correlated subquery

Single-row subquery in SQL:

The single-row subquery returns zero or one row to the outer query. These subqueries are used with the WHERE or HAVING statements.

Operators used in the Single-row subquery are :

Operator symbol Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Lesser than
<= Lesser than or equal to
=!,<> Not equal to

Working of the single-row subquery :

  • In the below image, there are 2 queries. One is the main query and another one is the subquery.
  • The first execution takes place in the subquery.
  • The result of the subquery is zero or a single row. (Hence the name single-row subquery).
  • This output passes to the main query and acts as the second execution (placed after the operator of the main query).

The condition of the main query is applied to the output of the subquery and acts as the third execution. This gives the output of the whole query.

single-row-flow-diagram-sql

Example for single-row subquery :

SELECT first_name, last_name, salary, age, gender
FROM persons_table
WHERE salary < (SELECT salary 
                FROM persons_table
                WHERE first_name='Soma');

In this query, the first_name, last_name, salary, age, and gender where first_name is 'Soma' in persons_table are selected.
retrieved-table
single-row-subquery-sql

Let us break the query into 2 parts and check what is going on.

Inner query :

SELECT salary 
FROM persons_table
WHERE first_name='Soma';

In this query, the salary should be selected where the first_name belongs to 'Soma'. When we use the above query, we get '30000' as an output ( i.e, the single row and the single column data). This output of the subquery will pass to the main query.
single-row-inner-query-sql

Main query :

SELECT first_name, last_name, salary, age, gender
FROM persons_table
WHERE salary < 30000;

Now, we got the output from the subquery. Based on the 'salary' as the condition, we can retrieve the required columns.
single-row-outerquery-sql

Multi-row subquery in SQL:

The multi-row subqueries return one or more rows and a single column to the outer query. This subquery is used with IN, ANY, or ALL operators.

Operators used in the multi-row subquery :

Operator symbol Operator meaning
< ANY less than the highest value
> ANY more than the lowest value
= ANY equivalent to IN
> ALL more than the highest value
< ALL less than the lowest value
IN Matching value
NOT IN Do not have the matching value

Working of the multi-row subquery :

  • Here there are 2 queries. One is the main query and another one is the subquery.
  • The first execution takes place in the subquery. The result of the subquery is one or more rows. (Hence the name multi-row query).
  • This output passes to the main query (placed after the operator of the main query).

The condition of the main query is applied to the output of the subquery. This gives the output of the whole query.
multi-row-flow-diagram-sql

Example for multi-row subquery :

SELECT person_id,first_name,last_name,salary,city
FROM persons_table
WHERE person_id IN( SELECT person_id 
                    FROM dept_table
                    WHERE dept_name='Software_Testing');

Two tables are used in this query. They are, persons_table and the dept_table. The persons_table is used for the outer query and the dept_table is used for the inner query.

The result of the dept_table is passed to the persons_table as the dept_table is subquery of the persons_table.
base-table-sql
multi-row

Let us divide the query to know how it works. The below query is the subquery.

SELECT person_id 
FROM dept_table
WHERE dept_name='Software_Testing';

In this subquery, 2 rows and a single column are retrieved.
multi-row-eg

The below query is the outer query.

SELECT person_id,first_name,last_name,salary,city
FROM persons_table
WHERE person_id IN(3,4);

The result of the subquery is passed to the outer query.
multi-row-nested

Correlated subqueries in SQL:

The subquery depends on the output value of the main query is known as the correlated subquery. So the correlated subquery cannot be executed independently as a simple subquery. Here the outer query is executed twice.

As we mentioned above, the subquery depends on the output of the outer query, the outer query is executed first. Then the output of the outer query is passed to the subquery.

In a correlated subquery, any type of operator is used. The most commonly used Operator in the Correlative subquery is EXISTS and NOT EXISTS.
correletated-execution-sql-1

  • In the above image, the Main query is executed and the output of the main query is passed to the subquery.

correletated-execution-sql-2

  • In the above image, the subquery is executed and the output of the subquery is passed to the main query.

correletated-execution-sql-3

  • In the above image, again the Main query is executed and the output of the main query is passed to the subquery.

Hence the correlated subquery is also called repeating subquery. In this type of subquery, every subquery is executed once for every row processed by the outer query.

This type of subquery includes the EXISTS operator to test the existence of data rows and NOT EXISTS operators to test the nonexistence of the data rows.

The correlated subqueries use Alias to represent the table name. Alias means creating a temporary name for the table. We can call the table and the columns by using the alias name.

In this case, the 'Alias' can be defined using the '.' operator. The alias of the table name must be defined in the FROM statement.

Example of the correlated subquery :

SELECT p.person_id, p.first_name,p.last_name,p.salary
FROM persons_table p
WHERE EXISTS (SELECT d.person_id
              FROM dept_table d 
              WHERE d.person_id=p.person_id);

In this query, the persons_table and the dept_table are having the alias name of 'p' and 'd' respectively. Let us divide the query.

Subquery :

SELECT d.person_id
FROM dept_table d 
WHERE d.person_id=p.person_id;

In this query, the p.person_id is not defined. So the output is an error.
correlated-function-sql

Main query :

SELECT p.person_id, p.first_name,p.last_name,p.salary
FROM persons_table p
WHERE EXISTS (SELECT d.person_id
              FROM dept_table d 
              WHERE d.person_id=p.person_id);

In this query, the p.person_id is defined in the outer query. The main query is executed first and the value of the outer query is passed to the subquery.
correlated-row-nested-sql

In the below image, the persons_id is the common column. So the person_id is represented in the below image. The main query's person_id and the inner query's person_id are compared to each other and the common value is retrieved in the output.
correlated-row-nested-table-sql

Let us check those outputs in the table :
correlated-row-subquery-tables-sql

Advantages of Subqueries

  • Subqueries are used when the outer query is based on the unknown value.
  • The subquery is a complex query. The complexity can be broken into a series of logical steps. This makes easy understanding and code maintenance.
  • The subquery is used to write another query in the outer query.
  • The subqueries can also be replaced in the complex joins and unions to make queries more understandable.
0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions