SQL | Subquery

In SQL a Subquery can be simply defined as a query within another query. In other words we can say that a Subquery is a query that is embedded in WHERE clause of another SQL query. Important rules for Subqueries:

• You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
• A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.
• The subquery generally executes first when the subquery doesn’t have any co-relation with the main query, when there is a co-relation the parser takes the decision on the fly on which query to execute on precedence and uses the output of the subquery accordingly.
• Subquery must be enclosed in parentheses.
• Subqueries are on the right side of the comparison operator.
• Use single-row operators with single row Subqueries. Use multiple-row operators with multiple-row Subqueries.

Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen to be used most frequently with SELECT statement as shown below:

SELECT column_name
FROM table_name
WHERE column_name expression operator 
    (SELECT column_name FROM table_name WHERE ...);

Sample Table:

DATABASE

STUDENT

Sample Queries:

• To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE table whose section is A

SELECT NAME, LOCATION, PHONE_NUMBER 
FROM DATABASE 
WHERE ROLL_NO IN (SELECT ROLL_NO 
                  FROM STUDENT 
                  WHERE SECTION='A');

• Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the DATABASE table of the student whose ROLL_NO is returned from inner subquery. Output:

• Insert Query Example:

Table1: Student1

Table2: Student2

• To insert Student2 into Student1 table:

INSERT INTO Student1 
SELECT * FROM Student2;

• Output:

• To delete students from Student2 table whose rollno is same as that in Student1 table and having location as chennai

DELETE FROM Student2 
WHERE ROLL_NO IN (SELECT ROLL_NO 
                  FROM Student1 
                  WHERE LOCATION = 'chennai');

• Output:

1 row delete successfully.

• Display Student2 table:

• To update name of the students to geeks in Student2 table whose location is same as Raju,Ravi in Student1 table

UPDATE Student2 
SET NAME='geeks' 
WHERE LOCATION IN (SELECT LOCATION 
                   FROM Student1 
                   WHERE NAME IN ('Raju', 'Ravi'));

• Output:

1 row updated successfully.

• Display Student2 table:

GeeksforGeeks

Improve