Correlated and scalar subqueries in SQL

This recipe explains correlated and scalar subqueries in SQL using SQL server

Correlated and scalar subqueries in SQL

In this tutorial, let us understand what are subqueries and what are the different types of subqueries with clear examples.

Generally, subqueries are nothing but a query within a query. There are a lot of ways in which we can write subqueries in a query. Subqueries can sometimes be called as inner queries and the main queries are called as outer queries. Subqueries can be inserted in various places like SELECT, FROM, or WHERE clause of the main query.

There are three main types of subqueries

  1. Scalar subquery : A subquery that returns just one row and one column. Or in general terms, a subquery that returns just one value
  2. Multiple row / column subqueries : A subquery that returns multiple rows or multiple columns or both.
  3. Correlated subquery : A subquery that depends on the results of the outer query.

Let us understand Scalar and Correlated subqueries in detail with an example in this tutorial.

In this tutorial we are using a simple employee table which has 7 columns and 49 rows. The sample data is shown below.

Scalar subqueries

Scalar subqueries are fairly easy to understand. Let us consider an example where we want to find all the employees from the employees table whose salary is greater than the average salary. The best way to find this out is by using the concept of scalar subquery. Take a look at the following query.

In the WHERE clause of the query, we can see another simple query which just returns the average of all the salaries. If this query is executed separately, the result obtained is 5818 which the average of all the salaries. This value can now be used as a condition for the WHERE clause in the outer query.

The result of the above query is

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_TYPE SALARY MANAGER_ID DEPARTMENT_ID
4 Michael Hartstein MK_MAN 13000 100 20
5 Pat Fay MK_REP 6000 201 20
6 Susan Mavris HR_REP 6500 101 40
7 Hermann Baer PR_REP 10000 101 70
8 Shelley Higgins AC_MGR 12008 101 110
9 William Gietz AC_ACCOUNT 8300 205 110
10 Neena Kochhar AD_VP 17000 100 90
11 Lex De Haan AD_VP 17000 100 90
12 Alexander Hunold IT_PROG 9000 102 60
13 Bruce Ernst IT_PROG 6000 103 60
17 Nancy Greenberg FI_MGR 12008 101 100
18 Daniel Faviet FI_ACCOUNT 9000 108 100
19 John Chen FI_ACCOUNT 8200 108 100
20 Ismael Sciarra FI_ACCOUNT 7700 108 100
21 Jose Manuel Urman FI_ACCOUNT 7800 108 100
22 Luis Popp FI_ACCOUNT 6900 108 100
23 Den Raphaely PU_MAN 11000 100 30
29 Matthew Weiss ST_MAN 8000 100 50
30 Adam Fripp ST_MAN 8200 100 50
31 Payam Kaufling ST_MAN 7900 100 50
32 Shanta Vollman ST_MAN 6500 100 50

Scalar subqueries can be used within various clauses such as SELECT, FROM, WHERE, JOIN, UPDATE, DELETE INSERT and so on. Let us try to reframe the same example by replacing the subquery within a different clause.

The above query also returns the same results as that of the previous query.

Correlated subqueries

Correlated subqueries can be used in scenarios where a subquery needs to be executed for each candidate row in the outer query. In correlated subqueries, the subquery returns a different set of results for each candidate row considered by the main query. This way the outer query also depends on the inner query.

The best way to understand this is by considering an example. In the previous example we saw how to fetch the employee details of those whose salary is greater than the overall average salary. In this example, let us try to find out the employees whose salary is greater than the average salary of their department.

In the above query, we can see that the DEPARTMENT_ID of the outer table is being used inside the WHERE clause of the inner query. Here, for each row corresponding to the outer query,we calculate the average salary of that department and compare it with the current salary.

The result of the above query is

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_TYPE SALARY MANAGER_ID DEPARTMENT_ID
4 Michael Hartstein MK_MAN 13000 100 20
23 Den Raphaely PU_MAN 11000 100 30
29 Matthew Weiss ST_MAN 8000 100 50
30 Adam Fripp ST_MAN 8200 100 50
31 Payam Kaufling ST_MAN 7900 100 50
32 Shanta Vollman ST_MAN 6500 100 50
33 Kevin Mourgos ST_MAN 5800 100 50
12 Alexander Hunold IT_PROG 9000 102 60
13 Bruce Ernst IT_PROG 6000 103 60
17 Nancy Greenberg FI_MGR 12008 101 100
18 Daniel Faviet FI_ACCOUNT 9000 108 100
8 Shelley Higgins AC_MGR 12008 101 110

Hope this tutorial was helpful!

What Users are saying..

profile image

Gautam Vermani

Data Consultant at Confidential
linkedin profile url

Having worked in the field of Data Science, I wanted to explore how I can implement projects in other domains, So I thought of connecting with ProjectPro. A project that helped me absorb this topic... Read More

Relevant Projects

Recommender System Machine Learning Project for Beginners-2
Recommender System Machine Learning Project for Beginners Part 2- Learn how to build a recommender system for market basket analysis using association rule mining.

Learn Object Tracking (SOT, MOT) using OpenCV and Python
Get Started with Object Tracking using OpenCV and Python - Learn to implement Multiple Instance Learning Tracker (MIL) algorithm, Generic Object Tracking Using Regression Networks Tracker (GOTURN) algorithm, Kernelized Correlation Filters Tracker (KCF) algorithm, Tracking, Learning, Detection Tracker (TLD) algorithm for single and multiple object tracking from various video clips.

NLP Project for Beginners on Text Processing and Classification
This Project Explains the Basic Text Preprocessing and How to Build a Classification Model in Python

Build Customer Propensity to Purchase Model in Python
In this machine learning project, you will learn to build a machine learning model to estimate customer propensity to purchase.

Hands-On Approach to Regression Discontinuity Design Python
In this machine learning project, you will learn to implement Regression Discontinuity Design Example in Python to determine the effect of age on Mortality Rate in Python.

Ola Bike Rides Request Demand Forecast
Given big data at taxi service (ride-hailing) i.e. OLA, you will learn multi-step time series forecasting and clustering with Mini-Batch K-means Algorithm on geospatial data to predict future ride requests for a particular region at a given time.

AWS MLOps Project to Deploy Multiple Linear Regression Model
Build and Deploy a Multiple Linear Regression Model in Python on AWS

Many-to-One LSTM for Sentiment Analysis and Text Generation
In this LSTM Project , you will build develop a sentiment detection model using many-to-one LSTMs for accurate prediction of sentiment labels in airline text reviews. Additionally, we will also train many-to-one LSTMs on 'Alice's Adventures in Wonderland' to generate contextually relevant text.

Deploy Transformer BART Model for Text summarization on GCP
Learn to Deploy a Machine Learning Model for the Abstractive Text Summarization on Google Cloud Platform (GCP)

Detectron2 Object Detection and Segmentation Example Python
Object Detection using Detectron2 - Build a Dectectron2 model to detect the zones and inhibitions in antibiogram images.