In the real world, there are times when we need a particular set of data but we don’t have the exact details for getting the data set. In such cases, we try to get the information from the available set of information that we have. To achieve such target we use SQL subquery.
SQL Subquery
SQL subquery is a query nested inside another query. Most of the subqueries are used with WHERE clause of a query.
SQL Subquery Rules
- A Subquery can be used with different SQL clauses like WHERE clause, HAVING clause and FROM clause.
- Subqueries can be used with SELECT, UPDATE, INSERT and DELETE statements also.
- The order of execution starts from subquery first then the main query.
- The Subquery must be enclosed in parentheses.
- An ORDER BY command cannot be used in a subquery, even though the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
- When a subquery is used along with a comparison operator it should be on the right side of the comparison operator.
SQL Subquery Syntax
|
1 2 3 |
SELECT column_name FROM table_name WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition); |
The query inside the parenthesis after the OPERATOR is the subquery and the query outside the parenthesis is the main query.
SQL Subquery Example
Let us consider the following two tables in order to understand Subquery in a better way.
Student
| Roll No | Name | Age | Gender |
|---|---|---|---|
| 1 | Amit | 12 | M |
| 2 | John | 13 | M |
| 3 | Diana | 14 | F |
| 4 | Henry | 15 | M |
Class
| Class | Section | Roll No |
|---|---|---|
| 6 | A | 1 |
| 7 | A | 2 |
| 8 | A | 3 |
| 9 | B | 4 |
Please find below the MySQL queries to create the tables and insert the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE `student` ( `RollNo` INT NOT NULL, `Name` VARCHAR(45) NULL, `Age` INT NULL, `Gender` VARCHAR(45) NULL, PRIMARY KEY (`RollNo`)); CREATE TABLE `class` ( `ClassNo` INT NOT NULL, `Section` VARCHAR(45) NULL, `RollNo` INT NULL, PRIMARY KEY (`ClassNo`)); INSERT INTO `student` (`RollNo`, `Name`, `Age`, `Gender`) VALUES (1, 'Amit', 12, 'M'), (2, 'John', 13, 'M'), (3, 'Diana', 14, 'F'), (4,'Henry', 15,'M'); INSERT INTO `class` (`ClassNo`, `Section`, `RollNo`) VALUES (6, 'A', 1), (7, 'A', 2), (8, 'A', 3), (9,'B', 4); |
Let us try to look into some examples using SQL subqueries.
-
- SQL subquery for getting all the student’s name with section ‘A’
|
1 2 3 4 |
SELECT Name FROM student WHERE RollNo IN (SELECT RollNo FROM class WHERE section = 'A') |
SQL SubQuery with IN operator
-
- SQL subquery with class greater than 7
|
1 2 3 4 |
SELECT * FROM student WHERE RollNo IN (SELECT RollNo FROM class WHERE ClassNo>7) |
| RollNo | Name | Age | Gender |
|---|---|---|---|
| 3 | Diana | 14 | F |
| 4 | Henry | 15 | M |
SQL subquery with Greater than operator