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

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.

Let us try to look into some examples using SQL subqueries.

    1. SQL subquery for getting all the student’s name with section ‘A’

 

where1

SQL SubQuery with IN operator

    1. SQL subquery with class greater than 7

RollNo Name Age Gender
3 Diana 14 F
4 Henry 15 M

SQL subquery with Greater than operator

By admin

Leave a Reply