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.
Table of Contents
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
CopySELECT 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.
Please find below the MySQL queries to create the tables and insert the data.
CopyCREATE 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’
- SQL subquery with class greater than 7
CopySELECT Name FROM student WHERE RollNo IN (SELECT RollNo FROM class WHERE section = 'A')
CopySELECT * FROM student WHERE RollNo IN (SELECT RollNo FROM class WHERE ClassNo>7)