What are nested queries in SQL?

Structured Query Language (SQL) provides a multitude of operations and functions for flexible queries. This ensures that the most accurate results are obtained. One function that SQL provides is a subquery, which is a query nested inside a larger query. A subquery can be used anywhere in the main query; its output is computed first, then used by the main query to get the required rows.

Suppose you want to see the ages of students studying a particular major, but the tables that hold the ages and majors of each student are separate. You would use a subquery to get the ID of the student who is studying the specified major first, and then the ages of those students will be returned using the IDs we get from the subquery.

Syntax

SELECT columnName

FROM tableName

WHERE column_Name operator (

    SELECT columnName2

    FROM tableName2

    WHERE condition

);

Let’s create two demo tables, student_age and student_major, that contain data on the ages and the majors of the students, respectively. Run the two blocks below to see the tables.

Code

Table: student_age

CREATE TABLE student_age(
  s_ID INT,
  s_name varchar(255),
  s_age INT
);
INSERT INTO student_age
VALUES (373, 'Chris', 19);
INSERT INTO student_age
VALUES (361, 'Bob', 21);
INSERT INTO student_age
VALUES (354, 'Kelly', 20);
INSERT INTO student_age
VALUES (391, 'Lisa', 18);
INSERT INTO student_age
VALUES (338, 'Rob', 19);
SELECT * FROM student_age;

Table: student_major

CREATE TABLE student_major (
  s_ID INT,
  s_name varchar(255),
  s_major varchar(255)
);
INSERT INTO student_major
VALUES (373, 'Chris', 'CS');
INSERT INTO student_major
VALUES (361, 'Bob', 'Psych');
INSERT INTO student_major
VALUES (354, 'Kelly', 'Econ');
INSERT INTO student_major
VALUES (391, 'Lisa', 'CS');
INSERT INTO student_major
VALUES (338, 'Rob', 'CS');
SELECT * FROM student_major;

Since the majors and ages of the students are not stored in one table but rather two separate ones, we have to use a subquery to get the ages of the students in CS. The subquery below returns the IDs of students who have 'CS' as their major, and the main query uses the output of the subquery to return the ages of those students. The IN operator is used because the subquery returns more than one row.

SELECT *
FROM student_age
WHERE s_ID IN (
  SELECT s_ID
  FROM student_major
  WHERE s_major='CS'
);
Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)