Working of Correlated SubQueries in SQL

Board Infinity -

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