Sql Subqueries
A subquery is a SQL query or statement or expression within a query. Subqueries are called nested queries or inner queires. It must be enclosed with parenthesis.
Subqueries are provide data or result to the enclosing query. It can return individual values or a list of rows to outer query or main query.
It can be nested into any valid sql statement, expression, or inside another subquery.
Related Links
Sql Subquery Syntax
There is no general syntax; subqueries are normal sql queries placed inside parenthesis. Subqueries can be used in many ways and at many locations inside a query or expression:
A subquery usually take one of these below formats:
- WHERE expression [NOT] IN (subquery)
- WHERE expression comparison_operator [ANY | ALL] (subquery)
- WHERE [NOT] EXISTS (subquery)
Sample Database Table - Employee
ID | EmpName | Designation | Dept | JoinYear | Salary |
---|---|---|---|---|---|
1 | Geetha | SQL Database | Oracle | 2013 | 10820 |
2 | Bala Murugan | Cloud Database | Oracle | 2015 | 11660 |
3 | Hanumanthan | Cloud Database | SQL Server | 2014 | 20270.8 |
4 | Ranjani Mai | Sql Head | PHP | 2013 | 8510.8 |
5 | Padmavathi | SQL Mining | SQL Server | 2013 | 3470.4 |
6 | Vinoth Kumar | Database Designer | SQL Server | 2014 | 14600 |
SQL Subquery With IN Operator
The following SQL statement will display records from employee table which is matched in the IN list.
SELECT * FROM Employee
WHERE Dept IN (SELECT Dept FROM Employee WHERE Salary < 10000)
In the above query, "SELECT Dept FROM Employee WHERE Salary < 10000" is a subquery and it return list of values to outer query.
The sub-query returns "PHP" and "Sql Server" for which employee "Salary" is less than "10000".
The result of above query is:
ID | EmpName | Designation | Dept | JoinYear | Salary |
---|---|---|---|---|---|
3 | Hanumanthan | Cloud Database | SQL Server | 2014 | 20270.8 |
4 | Ranjani Mai | Sql Head | PHP | 2013 | 8510.8 |
5 | Padmavathi | SQL Mining | SQL Server | 2013 | 3470.4 |
6 | Vinoth Kumar | Database Designer | SQL Server | 2014 | 14600 |
Related Links