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.
- ORDER BY command cannot be used in a Subquery. GROUPBY command can be used to perform same function as ORDER BY command.
- Use single-row operators with singlerow 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
NAME | ROLL_NO | LOCATION | PHONE_NUMBER |
---|---|---|---|
Ram | 101 | Chennai | 9988775566 |
Raj | 102 | Coimbatore | 8877665544 |
Sasi | 103 | Madurai | 7766553344 |
Ravi | 104 | Salem | 8989898989 |
Sumathi | 105 | Kanchipuram | 8989856868 |
STUDENT
NAME | ROLL_NO | SECTION |
---|---|---|
Ravi | 104 | A |
Sumathi | 105 | B |
Raj | 102 | A |
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:
NAME | ROLL_NO | LOCATION | PHONE_NUMBER |
Ravi | 104 | Salem | 8989898989 |
Raj | 102 | Coimbatore | 8877665544 |
- Insert Query Example:
Table1: Student1
NAME | ROLL_NO | LOCATION | PHONE_NUMBER | |
---|---|---|---|---|
Ram | 101 | chennai | 9988773344 | |
Raju | 102 | coimbatore | 9090909090 | |
Ravi | 103 | salem | 8989898989 |
Table2: Student2
NAME | ROLL_NO | LOCATION | PHONE_NUMBER | |
---|---|---|---|---|
Raj | 111 | chennai | 8787878787 | |
Sai | 112 | mumbai | 6565656565 | |
Sri | 113 | coimbatore | 7878787878 |
- To insert Student2 into Student1 table:
INSERT INTO Student1 SELECT * FROM Student2;
- Output:
NAME | ROLL_NO | LOCATION | PHONE_NUMBER | |
---|---|---|---|---|
Ram | 101 | chennai | 9988773344 | |
Raju | 102 | coimbatore | 9090909090 | |
Ravi | 103 | salem | 8989898989 | |
Raj | 111 | chennai | 8787878787 | |
Sai | 112 | mumbai | 6565656565 | |
Sri | 113 | coimbatore | 7878787878 |
- 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:
NAME | ROLL_NO | LOCATION | PHONE_NUMBER | |
---|---|---|---|---|
Sai | 112 | mumbai | 6565656565 | |
Sri | 113 | coimbatore | 7878787878 |
- To update name of the students to Beginner in Student2 table whose location is same as Raju,Ravi in Student1 table
UPDATE Student2 SET NAME=’Beginner’ WHERE LOCATION IN ( SELECT LOCATION FROM Student1 WHERE NAME IN (‘Raju’,’Ravi’));
- Output:
1 row updated successfully.
- Display Student2 table:
NAME | ROLL_NO | LOCATION | PHONE_NUMBER | |
---|---|---|---|---|
Sai | 112 | mumbai | 6565656565 | |
Beginner | 113 | coimbatore | 7878787878 |