13
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
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:
Learn SQL Server in the Easiest Way
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;
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) ;
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: Most Popular SQL Server Performance Tuning Tips
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];
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.
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. |
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.
SQL Server Training & Certification
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: Learn How to Add Column Using SQL Server ALTER TABLE ADD Column Statement
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.
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 –
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.
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.
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: SQL Server DBA Roles and Responsibilities: What Should You Know?
Subqueries are specified in multiple places. Let us discuss all types of a subquery in SQL Server one by one.
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.
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: MSBI Tutorial Guide for Beginners in 2024
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.
SQL Server Training & Certification
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:
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:
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:
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:
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
Disadvantages of using SQL Subquery
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: How to Create Database in Microsoft SQL Server?
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews