Nested Queries in SQL - Query In Query

Board Infinity -

Overview

S.Q.L. (Structured Query Language) is a language used to operate R.D.B.M.s (Relational Database Management System). A handy feature in SQL is nesting a query within another. The nested query is also referred to as a subquery.

This article aims at shedding some light on nested queries with the SELECT, DELETE, and INSERT statements, the usage of subqueries within WHILE clauses, etc. All code blocks in this article shall adhere to MySQL standards.

Scope

In this article we shall learn about:

  • What is a nested query?
  • How to create a nested query?
  • Some fine examples.

This article doesn’t concern itself with:

  • Any other unrelated SQL or DBMS concepts.

What is a nested query?

As the name suggests, a query nested within another query (as a part of it) is referred to as a nested query. There can be several levels of nesting, such that a query has a query nested in it, and the nested query (subquery) has itself in turn another query nested in it, and so on.

Let us say there is a query mainQuery having another query subQuery nested within it. subQuery is evaluated first, and then its results are made available to the mainQuery.

How to create a nested query?

Following are some common syntax patterns for creating a subquery:

SELECT item1, item2, …

FROM (

subQuery

) subQueryTableName;



SELECT item1, item2, …

FROM tableName

WHERE itemi IN (

subQuery

);


DELETE FROM tableName

WHERE itemi IN (

subQuery

);


SELECT item1, item2, …

FROM (

subQuery

) subQueryTableName;



Sub-queries can be used in aggregation, function calls, etc as well.

Examples

Let us create a dummy database and a table in it to be able to understand code demonstrations better.

CREATE DATABASE test;

USE test;

CREATE TABLE test(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE, name TEXT NOT NULL, employed BOOLEAN);

INSERT INTO  test(name, employed) VALUES('Albus Dumbledore', 1),('Darth Vader', 0),('Tywin Lannister', 1);

SELECT * FROM test;



write your code here: Coding Playground

Output

CREATE TABLE movieOrShow(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE, title TEXT NOT NULL, role TEXT NOT NULL UNIQUE);

INSERT INTO movieOrShow(title, role) VALUES('Harry Potter', 'Albus Dumbledore'),('Star Wars', 'Darth Vader'),('Game of Thrones', 'Tywin Lannister');

SELECT * FROM movieOrShow;

Output

  • The following code block prints the names of all the employed characters:

SELECT NAME from (

     SELECT * FROM test WHERE EMPLOYED

     ) NAMES;


The following code snippet dictates how to use a subquery in a WHERE clause:

SELECT id FROM test

WHERE name IN (

SELECT name FROM movieOrShow

);

write your code here: Coding Playground

Output

  • The following code block depicts how one may delete an entry from a table based on the result of a sub-query:

DELETE FROM test

WHERE NOT test.employed

AND test.name IN(

SELECT name FROM movieOrShow

);


SELECT * FROM test;



write your code here: Coding Playground

Output

Conclusion

  • A handy feature in SQL is nesting a query within another. The nested query is also referred to as a subquery.
  • There can be several levels of nesting, such that a query has a query nested in it, and the nested query (subquery) has itself in turn another query nested in it, and so on.