What is SQL Subquery? Types of Subqueries in SQL

Introduction

A Subquery, also named as the inner query or nested query is a query within another SQL query and embedded within the WHERE clause. A subquery helps to return data used by the main query as a condition to restrict the data retrieval further. Subqueries are majorly used with SELECT, INSERT, UPDATE, and DELETE statements along with comparison operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Here are the considerations that are followed by each subquery in the SQL:

• Usually, a subquery consists of a single column only with the SELECT clause unless there are multiple columns in the main query to compare its selected columns.
• The subqueries in SQL must be enclosed within parentheses.
• The subqueries in MySQL cannot use ORDER BY command while the main query can use ORDER BY command when required. Instead, you can use Group By command for the same purpose.
• If there is a subquery that returns multiple rows together, it must be used with multiple value operators only like an IN operator.
• The SELECT list does not include any references to values that evaluate to a BLOB, CLOB, ARRAY, or NCLOB, etc.
• We cannot enclose the subquery immediately in a set function.
• We cannot use the BETWEEN operator within a SQL subquery; However, it can be used with the main query.

Subqueries with SELECT Statement

SQL subqueries are majorly used with SELECT statement, and its basic syntax is given below.

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

The SELECT statement in SQL is used to fetch data from a database table, and this data is returned in the form of the result table. These result tables can also be named as result-sets. The basic syntax for the SQL SELECT statement is given below.

SELECT Column1, column2, column from table_name;

Here, column1, column2, is the fields of a table whose value you want to fetch. To fetch all fields together from a database table, you can use the following command:

SELECT * From Table_name;

Subqueries with INSERT Statement

After the SELECT statement, the second-best option to use with subqueries in MySQL is the INSERT clause.  The INSERT statement uses data returned from the subquery to enter into another table. The selected data in the subquery can be further modified through any of the characters, date, or number functions. The basic syntax of the INSERT statement within a subquery is given below.

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

In general, the SQL INSERT INTO statement is used to add new rows to an existing database table. Here column1, column2, is the name of columns where you want to add data. You don’t have to specify column names when you want to copy the whole table data together to a new table. Further, you can also populate the data of one table to another table using the SELECT statement in conjunction with the INSERT statement. You just have to give the other table as a set of fields that are needed to populate the first table. Take an example of Customer_New table whose structure is almost similar to the Customer table. You can use the following code to copy the complete data from the customer table to the Customer_New table.

SQL> INSERT INTO CUSTOMERS_BKP
   SELECT * FROM CUSTOMERS 
   WHERE ID IN (SELECT ID 
   FROM CUSTOMERS) ;

Subqueries with UPDATE Statement

The SQL subqueries are generally used in conjunction with the UPDATE statement. You can update multiple columns together when using a subquery with the UPDATE statement. The basic syntax for using the UPDATE statement with SQL subqueries is given below.

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

As we know, the UPDATE statement is used to modify an existing record within a database table. To modify selected rows, the WHERE clause should be used with the UPDATE statement; otherwise, it may disturb the overall functioning of the table. You can combine multiple conditions here using AND, OR operators.

Read: Power BI - Getting Started with Query Editor in Power BI

AND, OR Statements in SQL

The AND, OR operators in SQL are used to combine multiple conditions to narrow data in an SQL statement. These two operators are also named as conjunctive operators in SQL. These operators help conduct multiple comparisons with different operators in the same SQL statement.

AND operator allows the existence of multiple conditions in an SQL statement using the WHERE clause. For an action to be performed by the SQL statement, either it is a query or transaction, multiple conditions separated by AND must be true. The basic syntax of the AND operator in SQL is given below.

Read: How To Start Your Career As MSBI Developer?

SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition] AND [condition2]…… AND [conditionN];

Similarly, the OR operator allows the existence of multiple conditions in an SQL statement using the WHERE clause. For an action to be performed by the SQL statement, either it is a query or transaction, multiple conditions separated by OR must be true. The basic syntax of the OR operator in SQL is given below.

SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition] or [condition2]…… or [conditionN];

WHERE Clause in the SQL

The WHERE clause in SQL is used to specify a condition while fetching data from a single table or combining multiple tables together. If the given condition is true, then it returns the specific value from the table, otherwise, it returns NULL values. The SQL WHERE clause is basically used to filter the data and fetch necessary records. The basic syntax of the WHERE clause in SQL is given below.

SELECT Column1, Column2, ColumnN FROM table_name WHERE [condition];

You can use comparison or logical operators with WHERE clause to make it more meaningful.

Operators in SQL

An operator in SQL is a reserved word to perform a specific function and used along WHERE clause most of the time. They are used in conjunction with the WHERE clause to specify multiple conditions within a statement. Operators in SQL are majorly divided into four categories – Arithmetic, Logical, Comparison, etc. Let us discuss each of them one by one.

Arithmetic Operators	Description
Addition (+)	It is used to add values on either side of the operator.
Subtraction (-)	It is used to subtract right-hand operand from the left-hand operand.
Multiplication (*)	It is used to multiply values on either side of the operator.
Division (/)	It is used to divided left-hand operand by right-hand operand.
Modulus (%)	It is used to divided left-hand operand by the right-hand operand and returns the remainder.

A complete list of comparison operators in SQL is given below for your reference with the operator’s  name and its description.

Comparison Operator	What does it mean?
=	Equal to
<>	Not Equal to
!=	Not Equal to
<	Less Than
<=	Less than or Equal to
>	Greater Than
>=	Greater than or Equal to
LIKE '%expression%'	Contains 'Expression'
IN ('exp1', 'exp2', 'exp3')	Contains only of 'exp1', 'exp2' or 'exp3'

Here is the list of logical operators to use with SQL subqueries and WHERE clause.

Logical Operators	Description
ALL	It is used to compare the complete value set from another value set.
AND	AND operator allows the existence of multiple conditions in an SQL statement using the WHERE clause.
ANY	This operator is used to compare the particular value from the list as per the given condition.
BETWEEN	This operator is used to search values within a set of values where minimum are maximum values are given separately.
EXISTS	It is used to check the existence of a particular row based on the specified condition.
IN	It is used to compare a particular value from the list of literal variables
LIKE	It is used to compare similar values using wildcard operators.
NOT	It is used to reverse the meaning of a logical operator that is used with the statement.
OR	OR operator allows the existence of multiple conditions in an SQL statement using the WHERE clause
IS NULL	It is used to compare a specific value with the NULL value.
UNIQUE	This operator will check all rows for uniqueness. There should not be any duplicate values.

Subqueries with DELETE Statement

The subqueries in MySQL can be used in conjunction with the DELETE statement too like any other statement discussed above. The basic syntax is given below.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

As we know the DELETE statement is used to delete the existing records from a table. You can combine N number of conditions here using AND, OR operators. The depth idea of these two operators is already given earlier with examples for your reference. To delete a particular record from the table, you should use the WHERE clause. When you are using the WHERE clause, it will delete the selected data based on the condition; At the same time, if you want to delete the complete data from a database table, then you should not use WHERE clause in that case.

How to join subqueries in SQL?

If you can filter subqueries, then it is pretty obvious that you can join them as well. Here is the syntax for the same.

SELECT *
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date
           FROM tutorial.sf_crime_incidents_2014_01
          ORDER BY date
          LIMIT 5
       ) sub
    ON incidents.date = sub.date

It is more useful when combined with aggregations. When you are joining subqueries, you will notice that outputs are not so stringent as when you use the WHERE clause. For example, one particular inner query may produce multiple outputs together. The following query will rank the output based on how many incidents were reported on a given day. It can be done by aggregating the total number of incidents every day and using the same to sort the outer query.

SELECT incidents.*,
       sub.incidents AS incidents_that_day
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date,
          COUNT(incidnt_num) AS incidents
           FROM tutorial.sf_crime_incidents_2014_01
          GROUP BY 1
       ) sub
    ON incidents.date = sub.date
 ORDER BY sub.incidents DESC, time

You must be wondering where to use the subqueries. In practice, subqueries are useful for improving the overall performance of your queries. Imagine you want to aggregate the overall Company receiving investments and acquired by the Company each month. It is possible with the help of subqueries only. They take only minutes to return the final value and easy to manage as well.

Read: SQL Server Recovery Models-Simple, Full and Bulk Log

Basically, when you are joining every row in a given month from one table on to every month in a given row to another table, the overall number of rows returned is incredibly great. This multiplicative effect says that you should use Count (Distinct) operator here instead of using simply Count.  It helps you in aggregating two tables separately then join them together to perform the accurate counts across far smaller datasets.

Subqueries and UNIONs

The SQL UNION operator is generally used to combine result data from two or more SELECT statements by returning unique values only. For using UNION clause, each SELECT statement should have –

• The same number of columns
• The same number of expressions
• The similar data types
• And having them in the same order

They need not have in the same length. The syntax of the UNION operator in SQL is given below where a specified condition could be any expression based on your requirements.

SELECT Column1, [, Column2 ] 
FROM table1 [, table 2] 
[WHERE condition]
UNION
SELECT Column1, [, Column2 ] 
FROM table1 [, table 2] 
[WHERE condition]

If you want to combine results from two SELECT statements including duplicate values, then you should use UNION ALL operators in that case. The same rules will be applied to both the operators and its syntax can be written as below.

SELECT Column1, [, Column2 ] 
FROM table1 [, table 2] 
[WHERE condition]
UNION ALL
SELECT Column1, [, Column2 ] 
FROM table1 [, table 2] 
[WHERE condition]

You will see many subqueries in SQL examples where  Union operators can be used in conjunctionas well. Here is the syntax of how to use UNION operator with subqueries:

SELECT *
  FROM tutorial.crunchbase_investments_part1

 UNION ALL

 SELECT *
   FROM tutorial.crunchbase_investments_part2

Splitting a dataset into parts is not logical especially when data has to be passed through Excel at any point. These two parts are considered different when you apply operations on them. The best idea is applying operations on a complete dataset together using subqueries. Here you should use UNION ALL operator instead of UNION. Try it yourself; it is pretty easy suing UNIONS with subqueries.

What is a correlated subquery?

A correlated subquery refers to the subquery which relies on the outer query and is evaluated according to every instance of the outer query. It depends on the outer query, whereas a subquery does not depend on the outer query.

A subquery can be nested to multiple levels within a statement. Here, you should use the JOIN operator to connect multiple subqueries. In a few cases, queries are evaluated by executing the subquery once and putting the result value into the outer query.

Necessity for correlated subqueries in SQL:

This can be well explained with the help of an example. If we want to find the workers earning a salary greater than the average departmental salary, we will be using the below-mentioned query.

The point of difference between a SQL correlated subquery and a normal subquery is that correlated subqueries depend on and take references from the outer table. In the example given above, e1.dept_id is a reference to the outer subquery table.

Again let us take another example. If we want to get the names of departments with more than 10 workers, the following SQL correlated subquery can be deployed: 

Finally, coming to the last example, it has been mentioned that subqueries can be a part of WHERE, FROM, HAVING, AND SELECT clauses. Here, we will use a SQL correlated subquery in the SELECT list to identify the name of every worker, their respective salary, and the average earning of the department. We will retrieve the average income through the help of a correlated subquery inside the SELECT clause. The code is: 

For example, if there is one correlated subquery, then the inner subquery depends on the outer query for its values. Here, you have to execute the subquery repeatedly, once for each row that might be selected by the outer query later.

USE AdventureWorks2016; GO SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ; GO

Here is the result set.

LastName FirstName BusinessEntityID -------------------------- ---------- ------------ Ansman-Wolfe Pamela 280 Saraiva José 282 (2 row(s) affected)

The previous subquery in this statement is not evaluated independently of the outer query. This is exactly the right way how subqueries are executed and evaluated when they are correlated.

Correlated Subqueries

A subquery can be nested to multiple levels within a statement. Here, you should use the JOIN operator to connect multiple subqueries. In a few cases, queries are evaluated by executing the subquery once and put the result value into the outer query.

Read: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key

For example, if there is one correlated subquery, then inner subquery depends on the outer query for its values. Here, you have to execute the subquery repeatedly, once for each row that might be selected by the outer query later.

USE AdventureWorks2016;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

Here is the result set.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)

The previous subquery in this statement is not evaluated independently of the outer query. This is exactly the right way how subqueries are executed and evaluated when they are correlated.

Read: Snapping a Picture of a Database-Database snapshot

Types of Subqueries in SQL Server– What you should know?

Subqueries are specified in multiple places. Let us discuss all types of a subquery in SQL Server one by one.

A). Subqueries with Aliases

This is the first in the list of types of subqueries in SQL. Many subqueries where the inner query and the outer query refer to the same table, they are connected by self-joins. Here, table aliases are required when the same table used for two different roles in the same query. Aliases are also used in nested queries that refer to the same table for inner and outer queries.

B). Subqueries with IN

When subqueries are used with an IN operator, it may return zero or multiple values depending on conditions. Once the subquery returns the result, it is used by the outer query. The same result can be produced with JOIN operator too, but you have to make sure where to use subquery and where to use JOIN versions.A join can always be expressed as a subquery. At the same time, a subquery can be expressed as a join sometimes not always. This is because joins are symmetric, you can join two tables in either order, results will be the same. The same is not true when the subquery is involved.

Read: A Complete Overview Of SQL Not Equal Operator with Examples

C). Subqueries with Not IN

When subqueries are used with NOT IN operator, it may return zero, or multiple values depending on conditions. The subquery with NOT IN operator cannot be converted to a join because joins have different meanings in this case. Moving ahead, subqueries are used with many more operators and most of them we have discussed in the blog like SELECT, UPDATE, INSERT, DELETE, UNION, conditional logic and more.

D). Subqueries with a single row

Single-row subqueries refer to the subqueries which return a single row as an output to the parent query. Single-row subqueries are utilized in a SQL SELECT statement with HAVING clause, WHERE clause, or a FROM clause. Let's see an example of the same where the single-row subqueries have been used in the SELECT statement.

The SQL query shows the average price of all paintings with respect to the real price. The result resembles the following table: 

Single-row subqueries are also deployed with the WHERE clause in the SELECT statement to filter the results related to the outer query. Here’s an example.

This SQL query depicts the records of the salespersons whose agency fee is higher than the average of all the fees. The subquery in the statement calculates the average agency fee which is 2728. The parent query utilizes the returned value to filter the data of the salespeople who get greater than average agency fees. The result resembles the following table: 

E) Multiple row subquery

Subqueries which return multiple rows as a result to the parent statement are termed as multiple row subqueries which are used in a SQL SELECT statement including a HAVING clause, WHERE clause, a FROM clause and a logical operator. Lets see an example of the same.

This SQL query calculates the average agency fee of those sales agents who are not managers, and the subquery returns the list of IDs of all managers. Finally, the outer query filters the table to search the sales agents who are not managers and find their average agency fee of them. It returns a single average value of the agency fee. The output resembles the following: 

F). Subqueries with multiple columns

These refer to the subqueries which return multiple columns as a result of the parent query. Here’s an example: 

This SQL query finds the painting with the lowest price. The inner subquery returns the record of the painting with the lowest price. The outer query compares the records utilizing the IN operator and returns the one with the lowest price. The output looks like this: 

G) Nested subqueries

These refer to the subqueries that are embedded inside another subquery. The subqueries are executed at every stage, where the inner one is executed at the initial stage and then followed by the outer ones. Here’s an example of the same:

This SQL query shows the average price of paintings whose price is higher than 5000 than the real price. The output resembles this: 

Advantages and disadvantages of using SQL subqueries

Using SQL Subquery in SQL Server come with its own sets of advantages and disadvantages. Let’s discuss these pros and cons individually: 

Advantages of using SQL Subquery

• They break down the complicated query into different fragments so that a complex query is divided into a series of logical parts.
• It is simple to comprehend and eases the process of maintaining the code.
• The output of another query can be utilized in the outer query.
• Subqueries can act as a substitute for complicated joins.

Disadvantages of using SQL Subquery

• A statement that deploys a subquery can be executed in a faster way if rewritten as a union because the optimizer is more efficient for MYSQL for joins as compared to subqueries.
• A table cannot be altered and selected from the same table within a subquery in the same SQL statement. 

Final Words:

To understand this concept a little better, you can do the research at your own part otherwise we tried to cover the maximum information in the blog to help you with tough database subqueries.

We strongly encourage you to play with syntax and run queries without some of the given operators and check how they work. You may also run each of the subqueries independently and understand how they can improve the database performance for your Company. To know more about subqueries in SQL, you may join the SQL server course at JanBask training and get in touch with us.

Read: Difference Between Clustered and Non-Clustered Index in the SQL

FaceBook Twitter LinkedIn Pinterest Email