Mastering SQL: From Fundamentals to Advanced Techniques

Nested Queries in SQL - Query In Query

Board Infinity
Board Infinity
Nested Queries in SQL - Query In Query

    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

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.