limited time offer

What Is The Purpose Of A Sub Query in SQL ?

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Example of SubQuery

Student Table

+-----------+-------+
| StudentId | Name  |
+-----------+-------+
|         1 | Tarun |
|         2 | Ram   |
|         3 | Sham  |
|         4 | Mohan |
+-----------+-------+

Marks Table

+-----------+------------+
| StudentId | TotalMarks |
+-----------+------------+
|         1 |        500 |
|         2 |        400 |
|         3 |        300 |
|         4 |        700 |
+-----------+------------+

Query

SELECT s.StudentId, s.Name, m.TotalMarks 
FROM sys.student s, sys.marks m 
WHERE s.StudentId = m.StudentId 
AND m.TotalMarks > (SELECT TotalMarks FROM sys.marks WHERE StudentID = 2);

Result

+-----------+-------+------------+
| StudentId | Name  | TotalMarks |
+-----------+-------+------------+
|         1 | Tarun |        500 |
|         4 | Mohan |        700 |
+-----------+-------+------------+