SQL Subquery – Nested Query in SQL

Filed Under: SQL

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

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.

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.

  1. SQL subquery for getting all the student’s name with section ‘A’
  2. 
    SELECT Name FROM student 
    WHERE RollNo IN (SELECT RollNo FROM class WHERE section = 'A')
    
    Name
    Amit
    John
    Diana
    SQL nested query example

    SQL SubQuery with IN operator

  3. SQL subquery with class greater than 7
  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 where clause

SQL subquery with Greater than operator

Comments

  1. Monisha says:

    Very good explaination with example we can easily understand it

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages