SQL Subquery | SQL Beginner Guide

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)

Result is

 

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

  1. Inner query
  2. 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)

 

Outer Query


SELECT [CourseID]
,[Title]
,[Credits]
,[DepartmentID]
FROM [School].[dbo].[Course]
WHERE [DepartmentID] IN

 

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.

Independent Subquery

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.

Dependent Subquery

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)

Correlated subquery

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 SQL Subquery

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

 

SQL Subquery Correlated example

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.

Happy Learning!
Cheers

Advertisements

Leave a Reply

%d bloggers like this: