Mastering SQL: From Fundamentals to Advanced Techniques

Working of Correlated SubQueries in SQL

Board Infinity
Board Infinity
Working of Correlated SubQueries in SQL

    Introduction To SQL: A Complete Guide

    SQL Commands-An Introduction

    Char and Varchar in SQL

    ORDER BY in SQL

    5 Resume Writing Myths That You Need to Stop Following Now!

    GROUP BY in SQL

    Learn How to Perform Union in SQL?

    Joins and Its Types in SQL

    Rename column name in SQL

    Learn How To Compare Two Or More Datetime Entities In SQL With Examples

    Difference Between SQL and NoSQL

    Constraints in SQL

    Learn about Aggregate Functions in SQL

    SQL Subqueries

    Nested Queries in SQL - Query In Query

    SQL WITH Clause – Tutorial and Examples

    How to Drop a Table in PostgreSQL?

    How to Perform Pivot in SQL?

    SQL Window Function

    Find Nth Highest Salary in SQL

    Working of Correlated SubQueries in SQL

    Learn about Stored Procedures in SQL

    Triggers in SQL: An Overview and Example

    PL/SQL Explained

Overview

In this article, we shall learn about correlated subqueries in SQL. A correlated query is a query nested within another query, such that the outer query is dependent upon the result produced by the inner query after its execution.

Scope

In this article we shall LEARN ABOUT:

  • SQL correlated subquery in WHERE clause.
  • Example of SQL correlated subquery in SELECT clause.
  • SQL correlated subquery with EXISTS operator.

SQL correlated subquery in WHERE clause

Let us first create a database and a table in it for use throughout this article.

CREATE DATABASE test;


USE test;


CREATE TABLE test(

ID INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, 

NAME TEXT NOT NULL,

SALARY INT NOT NULL,

MARITAL_STATUS BOOLEAN

);


CREATE TABLE qualified(

ID INT NOT NULL UNIQUE PRIMARY KEY,

NAME TEXT NOT NULL

);


INSERT INTO test(NAME, AGE, SALARY, MARITAL_STATUS)

VALUES("DEEP", 34, 10000, 0),

("HOMELANDER", 40, 100000, 1),

("ANNIE JANUARY", 26, 20000, 0),

("SOLDIER BOY", 100, 800000, 1);


INSERT INTO qualified(ID, NAME)

VALUES(2, "HOMELANDER"),

(4, "SOLDIER BOY");


write your code here: Coding Playground

Now let us have a look at the code block example below to understand how correlated subquery can be used in a WHERE clause:

SELECT * FROM test

WHERE SALARY > (

SELECT AVG(SALARY) FROM test

);

Output

+----+-------------+--------+----------------+-----+
| ID | NAME        | SALARY | MARITAL_STATUS | AGE |
+----+-------------+--------+----------------+-----+
|  3 | SOLDIER BOY | 800000 |              1 | 100 |
+----+-------------+--------+----------------+-----+
1 row in set (0.001 sec)

Working

  • The inner query returns the average of the salaries of all the entries in the test table.
  • The outer query makes a comparison of each row in the test table.
  • If for a given row, its salary is greater than the average, its column values are printed.

SQL correlated subquery in SELECT clause

Let us have a look at the code block ahead to better understand the working of correlated subqueries within a SELECT clause.

SELECT SALARY, (

SALARY - (

SELECT AVG(SALARY) FROM test

)

) DIFFERENCE_WITH_AVERAGE_SALARY

FROM test;


Output

+--------+--------------------------------+
| SALARY | DIFFERENCE_WITH_AVERAGE_SALARY |
+--------+--------------------------------+
|  10000 |                   -222500.0000 |
| 100000 |                   -132500.0000 |
|  20000 |                   -212500.0000 |
| 800000 |                    567500.0000 |
+--------+--------------------------------+
4 rows in set (0.000 sec)

Working

  • The inner query returns the average of the salaries of all the entries in the test table.
  • The outer query calculates the difference between the average salary and the current row’s salary.
  • The computed result is printed, along with the original salary from the row.

SQL correlated subquery with EXISTS operator

SELECT AGE, SALARY FROM test

WHERE EXISTS (

SELECT NAME FROM qualified

WHERE qualified.ID = test.ID

);

Output

+-----+----------------+
| AGE | MARITAL_STATUS |
+-----+----------------+
|  40 |              1 |
| 100 |              1 |
+-----+----------------+
2 rows in set (0.000 sec)

Working

  • The code above simply prints the salary and age of throws rows that are “qualified”!