What is a Subquery in SQL & How to write Subquery SQL with example

In this article, we will explain What is a subquery SQL and how to use subqueries in SQL Server (Transact-SQL) with syntax and example
Furthermore, in SQL Server, a subquery is a query within a query. you are free to create subqueries within your SQL statements. therefore, these subqueries can reside in the SELECT clause, WHERE clause, and the FROM Clause.

A subquery SQL is a SQL query nested inside a larger query.

The subquery SQL may occur in :

– A SELECT clause
– A FROM clause
– A WHERE clause

Therefore, you’ll be able to use a subquery in a SELECT, INSERT, UPDATE, or DELETE statement to perform the subsequent tasks:
To compare an expression to the result of the query.
Determine if an expression is included within the results of the query.
Check whether or not the query selects any rows.

Syntax :

Here, the subquery (inner query) executes once before the main query (outer query) executes.
The main query (outer query) uses the subquery result.

SELECT clause

A subquery can also be found within the SELECT clause. These are used when you would like to retrieve a calculation using an aggregate function such as the MIN, MAX, SUM, or COUNT function, furthermore, you do not want the aggregate function to apply to the main query.
Example

Therefore, in this example, we’ve created a subquery in the SELECT clause such as follows:

here, the subquery has been aliased with the name such as subquery2. furthermore, this will be the name used to reference this subquery or any of its fields.
therefore, the trick to placing a subquery in the select clause is that the subquery must return a single value. so, this is why an aggregate function such as the MIN, MAX, SUM, or COUNT function is commonly used in the subquery.

FROM clause

A subquery also can be found in the FROM clause that why it is also called inline views.
Example:

Therefore In the example, we’ve created a subquery in the FROM clause such as follows:

Therefore, as you see, this subquery has been aliased with the name subquery1. so, this will be the name used to reference this subquery or any of its fields.

WHERE clause

Therefore, Most often, the subquery will be found in the WHERE clause. so, these subqueries are also known as nested subqueries.

as in the above, the subquery portion of the SELECT statement is:

Therefore, as per the Subquery SQL Code, this subquery allows you to find all products_id values from the inventory table that have a quantity greater than 20.
so, the subquery is then used to filter the results from the main query using the IN condition.
furthermore, this subquery could have alternatively been written such as an INNER join as follows:

Therefore, These INNER joins would run more efficiently than the original subquery. so, it is important to note, though, that not all subqueries can be rewritten using joins

Subquery SQL Example

Let’s see the Subquery in SQL example, you will learn the requirements of using subqueries. therefore, We have the following two tables 'Employee' and 'Salary' with common field 'EmployeeID'.

As per given above both tables, now we want to write a query to identify all Employee who gets a better salary than that of the employee who’s EmployeeID is ‘E115’, but we do not know the Salary of ‘E115’.
Furthermore, to solve the problem, here, we require two queries. such as One query returns the salary (stored in Salary field) of ‘E115’ and a second query identifies the Employee who gets a better salary than the result of the first query.

SQL Query

Output:

The result of the above query is 10000.
Therefore, Using the result of this query, here we have need to written another query to identify the Employee who get better Salary than 10000. so,here is the query as given below :

Second Query

Output:

Therefore, as you see, the above two queries identified Employees who get a better salary than the Employee who’s EmployeeID is ‘E115’ (Anand Kumar Singh).

furthermore, we combine the above two queries by placing one query inside the other. so, the subquery (also called the ‘inner query’) is the query inside the parentheses. furthermore, let see the following code and query Output result :

SQL Query

Output:

Subquery SQL: General Rules

A subquery SELECT statement, which is almost similar to the SELECT statement, and it is used to begin an outer or regular query.
Therefore, given below is the syntax of a subquery:

General Syntax:

Type of SQL Subquery

First: Single row subquery: its returns zero or one row.
Second: Multiple row subquery: it, returns one or more rows.
Third: Multiple column subqueries: it, returns one or more columns.
Fourth: Correlated subqueries: The reference one or more columns in the outer SQL statement. so, the subquery is called a correlated subquery because the subquery is related to the outer SQL statement.
Fifth: Nested subqueries: The Subqueries are placed within another subquery.

Subquery SQL: Guidelines

There are some important guidelines to consider when using subqueries :

• A subquery must be enclosed in parentheses and subquery must be placed on the right side of the comparison operator.
• The subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added into a subquery.
  so, You can use an ORDER BY clause in the main SELECT statement (outer Query) which will be the last clause. use the single-row operators with single-row subqueries.
• If a subquery (inner-Query) returns a null value to the outer query, then the outer query does not return any rows once using comparison operators in a WHERE clause.

you can use a subquery inside INSERT, DELETE and UPDATE statement. let’s discuss in a breif:

Subqueries with INSERT statement

Therefore, INSERT statement can be used with subqueries. furthermore, there are the syntax and an example of subqueries using INSERT statement.

General Syntax:

If we want to insert those salary from 'Salary_Details' table which have the advance_salary 4000 or 9000
into 'advanced_amount' table the following SQL can be used:

Sample table: Salary_Details

SQL Code:

Output:

Subqueries with UPDATE statement

Therefore, In an UPDATE statement, you can set a new column value equal to the result returned by a single row subquery. furthermore, there are given below syntax and an example of subqueries using the UPDATE statement.

General Syntax:

If we want to update that Salary_Date in ‘Advanced_Amount’ table with ‘2016-06-11’ which have the difference of Total_Salary and Advance_Salary is less than
the minimum Total_Salary of ‘Salary_Details’ table the following SQL can be used:

Sample table: Advanced_Amount

EmployeeID	Name	Total_Salary	Advanced_Salary	Salary_Date	Dept_Code
E111	Mark Josheph	10000	4000	2016-01-02	M221
E112	Nitin Kumar Singh	9000	4000	2016-02-23	M223
E117	Mahi Kashyap	12000	4000	2016-04-01	M229
E120	Mukesh jain	14000	4000	2016-04-14	M235
E121	Dinesh Sharma	25000	9000	2016-04-18	M236

SQL Code:

Output:

Subqueries with DELETE statement

The DELETE statement can be used with subqueries. therefore, there are the given below syntax and an example of subqueries using the DELETE statement.

General Syntax:

If we want to delete those salary from ‘Advanced_Amount’ table which Advanced_Salary are less than the maximum Advanced_Salary of ‘Salary_Details’ table, therefore, the following SQL can be used:

Sample table: Advanced_Amount

EmployeeID	Name	Total_Salary	Advanced_Salary	Salary_Date	Dept_Code
E111	Mark Josheph	10000	4000	2016-01-02	M221
E112	Nitin Kumar Singh	9000	4000	2016-02-23	M223
E117	Mahi Kashyap	12000	4000	2016-04-01	M229
E120	Mukesh jain	14000	4000	2016-04-14	M235
E121	Dinesh Sharma	25000	9000	2016-04-18	M236

SQL Code:

Output: