Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

What are nested queries in SQL?

Eman Kashif

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'
);

RELATED TAGS

sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring