Table of Contents
In this article, we are going to learn how to use a subquery, a query inside another one, which is a concept that you will use a lot when writing queries in most Database engines.
SubQuesries in SQL
Subqueries are very efficient to handle queries that depend on the results from another query. Below are some of the characteristics of a subquery:
- A subquery must appear within parentheses. Example:
SELECT * FROM Employees WHERE DepartmentID IN (SELECT Id FROM Departments WHERE Name ='IT' )
- A subquery is a query, that is nested inside another query, within the SELECT, INSERT, UPDATE or DELETE statement.
- A subquery cannot include
FOR BROWSE
orCOMPUTE
clause. - A subquery cannot include an
ORDER BY
clause unless aTOP
clause is used to select the data. - A subquery should return only one column and one record if it’s used to compare values in the where clause. Example:
SELECT * FROM Employees WHERE Salary = (SELECT top 1 Salary from EmoployeeSalaries) -- Subquery always return one row and one column
- Also known as a correlated subquery, a subquery should only return one column if it’s used in a select statement as a column. Example:
SELECT Name, Salary, (SELECT Name from Departments d where d.ID = e.DepartmentID ) AS DepartmentName FROM Employees e -- Sub query always return one row and one column
Example of Subqueries
Given the two tables below:
CountryList
ID | Country | Population |
1 | USA | 400,000,000 |
2 | Egypt | 100,000,000 |
3 | Canada | 36,000,000 |
4 | Sweden | 13,000,000 |
CovidCases
ID | CountryID | Cases |
1 | 2 | 15,000 |
2 | 3 | 10,000 |
3 | 4 | 33,000 |
4 | 1 | 48,000 |
Example 1:
Get how many CODID-19 cases in countries that have more than 50 million in population.
SELECT Cases FROM CovidCases WHERE CountryID IN (SELECT ID FROM CountriesList WHERE Population > 50000000))
Result
[adinserter block=”2″]Cases
15,000
48,000
Example 2:
Correlated subquery, get how many Covid19 cases a country has
SELECT cl.Country, (SELECT Cases from CodingCases WHERE CountryID = cl.ID) FROM CountryList cl
Result
Country Cases
USA 48,000
Egypt 15,000
Canada 10,000
Sweden 33,000

IT specialist Veteran