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 the 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 BROWSEorCOMPUTEclause. - A subquery cannot include an
ORDER BYclause unless aTOPclause 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
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