SQL

An introduction to Subqueries in SQL

By Asha S.Posted on
Time to Read:-words

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 or COMPUTE clause.
  • A subquery cannot include an ORDER BY clause unless a TOP 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

Cases 
15,000
48,000

[adinserter block=”2″]

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

Post Views: 64