SQL Subquery is another tool SQL Server has which can be used to fetch data from other tables to be used in main query according to the needs. Inner query and nested query are other names of SQL Subquery.
SQL Subquery can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
The idea is that a subquery returns data for one column which can be further utilised in outer query.
Too much to read , let’s just start with examples and we will clear the points as we go. Sounds fair?
Prepare the data
Examples and queries that we will see here will run on our University Management System database . If you want to use these SQL queries for practice , create the same database , you need to just copy paste the script from here .
SQL Subquery Examples
Take a look at the tables and data we have and then see the query :
We want to see the courses who belong to a department with budget more than 200000.00!
SELECT [CourseID] , [Title] ,[Credits] ,[DepartmentID] FROM [School].[dbo].[Course] WHERE [DepartmentID] IN ( SELECT [DepartmentID] FROM [Department] WHERE [Budget] > 200000.00)
This query will return all departments with budget more than 200000.00!. To understand this clearly , let’s discuss these terms and see how they exist in an SQL query
- Inner query
- Outer Query
From the above SQL queries you can find these three
SQL Subquery/Inner Query/Nested Query
SELECT [DepartmentID] FROM [Department] WHERE [Budget] < 200000.00
SELECT [CourseID] ,[Title] ,[Credits] ,[DepartmentID] FROM [School].[dbo].[Course] WHERE [DepartmentID] IN (*InnerQuery*)
If you read carefully the complete query you will realise that we have basically filtered the data from other table and used the result rows in WHERE clause. Here is how SQL Server handles this
Our SQL Subquery produced this
These two rows then were matched one-by-one with outer query
One could argue that the same can be achieved by SQL Joins , you are right . SQL Joins could give us much more flexibility than SQL Subquery but it depends highly on the situation as well as requirements .
See : SQL Joins Explained
When to Use SQL Subquery versus a Standard Join?
There cannot be any universal rule to answer this question . We can adopt some best sql practices to make sure that we don’t end up worrying about tuning the same basic SQL queries a year later. A stage where you have to spend a good amount of time to go through the whole thing.
More often than not main concern of DBAs and data developers is performance of SQL query. So use of SQL Subquery or SQL Join can be decided if we keep performance as our deciding factor.
If a subquery can be executed without outer query entirely it’s own then it’s independent subquery. Such queries are evaluated once and the result is then used by outer query.
If a subquery cannot be executed without outer query and you get a syntax error because it refers to some tables outside of the subquery then its a dependent subquery. Such queries can be very slow . Reason is that they have to get the values from outer query as well. This with example is discussed at the bottom in correlated query exmaple . So if a outer table has 10,000 rows the inner query will be executed 10,000 times.
To answer our question many DBAs avoid dependant subquery and change it to JOIN .
Hopefully , after this you can decide which tool to pick and what to do with such a scenario.
Types of SQL Subquery
Following are few types of SQL subquery with exmaples
Single row subquery : Returns zero or one row.
SELECT [DepartmentID] ,[Name] ,[Budget] ,[StartDate] ,[Administrator] FROM [School].[dbo].[Department] WHERE [Administrator]= ( SELECT PERSONID FROM PERSON WHERE PERSONID=2 )
This produces only one row.
Multiple row subquery : Returns one or more rows.
SELECT [CourseID] ,[Title] ,[Credits] ,[DepartmentID] FROM [School].[dbo].[Course] WHERE [DepartmentID]IN ( SELECT [DepartmentID] FROM [Department] WHERE [Budget] < 200000.00)
Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because it is related to the outer SQL statement.
From our previous articles How Grouping Works in SQL . We use the example where we concluded the total marks of each student. It looked like this
Can you find me the students who has above average grades? Use this query
SELECT FirstName, TotalGrades FROM ( SELECT P.FirstName,SUM(GRADE) AS TotalGrades FROM [School].[dbo].[StudentGrade] SG LEFT JOIN [School].[dbo].Course C ON C.CourseID=SG.CourseID LEFT JOIN [dbo].Person P ON P.PersonID=SG.StudentID GROUP BY P.FirstName) A WHERE A.TotalGrades > ( SELECT AVG(GRADE) AS TotalGrades FROM [School].[dbo].[StudentGrade] SG )
Run this query and it will return students with above average marks.Also this is dependant query
How to Use SQL Subquery with SELECT Statement
SELECT [CourseID] ,[Title] ,[Credits] ,[DepartmentID] FROM [School].[dbo].[Course] WHERE [DepartmentID] IN ( SELECT [DepartmentID] FROM [Department] WHERE [Budget]< 200000.00)
How to Use SQL Subquery with the INSERT Statement
INSERT INTO [dbo].[Department] ([DepartmentID] , [Name] , [Budget], [StartDate], [Administrator]) VALUES (5, 'Computer', 800000, '2007-09-01 00:00:00.000', ( SELECT PERSONID FROM PERSON WHERE FIRSTNAME='Awais'))
Use SQL Subquery with the UPDATE Statement
UPDATE [dbo].[Department] SET [Name] = 'Computer Science' WHERE [Administrator] =(SELECT PERSONID FROM PERSON WHERE LastName='Harui')
Use SQL Subquery with the DELETE Statement
DELETE [dbo].[Department] WHERE [Administrator] =(SELECT PERSONID FROM PERSON WHERE LastName='Harui')
SQL Subquery Rules :
- SQL Subquery must be enclosed within parentheses.
- Can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
- An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
- SQL Subquery that return more than one row can only be used with multiple value operators such as the IN operator.
- A subquery cannot be immediately enclosed in a set function.
- The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.
This is pretty much about subqueries and if you find it useful . Share it with your firends and colleagues and let us know about how effective it is.
We are moving towards advanced topics now and also taking up the guests posts as well. So feel free to contact and have a good time learning.