Menu
Learn Coding Online – CodingPanel.com
  • Technologies
    • .NET Core
    • Angular
    • ASP.NET
    • C
    • C#
    • C++
    • Java
    • JavaScript
    • JSON
    • Kotlin
    • Node.js
    • PHP
    • Python
    • React
    • SQL
  • Topics
    • Algorithms
    • Computer Networking
    • Computer Science
    • Design Patterns
    • Software Design
    • Software Engineering
    • Web Development
  • Interview Questions
    • C# Interview Questions
  • Learn
    • My Learning
    • Data Structure
    • Programming
  • Test your Skills
Learn Coding Online – CodingPanel.com

An introduction to Subqueries in SQL

Posted on February 25, 2021March 17, 2021 by Asha S.

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

IDCountry Population
1USA400,000,000
2Egypt100,000,000
3Canada36,000,000
4Sweden13,000,000

CovidCases

IDCountryIDCases 
1215,000
2310,000
3433,000
4148,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

Related posts:

  1. Introduction to SQL Views
  2. SQL Wildcard Characters and Their Usage
  3. How to convert SQL query to LINQ

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Blog Authors

avatar for Asha S.Asha S. (4)

IT specialist Veteran

avatar for CodingPanel EditorCodingPanel Editor (40)

Blog Hero

avatar for Feras SFeras S (5)

Software Engineer

©2021 Learn Coding Online – CodingPanel.com | Powered by WordPress & Superb Themes