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.
SELECT columnName FROM tableName WHERE column_Name operator ( SELECT columnName2 FROM tableName2 WHERE condition );
Let’s create two demo tables,
student_major, that contain data on the ages and the majors of the students, respectively. Run the two blocks below to see the tables.
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;
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' );
View all Courses