Let’s understand the concept of Subquery in SQL. In SQL, Subquery is the query in the query. A subquery is also called nested query or inner query. SQL subquery is usually added in the WHERE Clause of the SQL statement.
The inner query executes first before its parent query so that the results of the inner query can be passed to the outer query.
SQL Syntax :
Select column1, column2
WHERE expression (Select select_list from table);
The subquery (inner query) executes once before the main query (outer query) executes.
The main query (outer query) use the subquery result.
SubQuery to Solve problem – Who has a salary greater than Miriella ?
Sol – Main Query [ Which employees have salaries greater than Miriella ]
Sub Query [ What is Miriella’s Salary]
SQL> Select First_name, Salary from employees
WHERE Salary > (Select salary from employees where First_name = Miriella);
In the above example, subquery will send the Miriella’s salary to outer query and outer query use the subquery result to find the salaries of the employees who are getting more than Miriella.
Subquery in SQL: Guidelines
There are some guidelines or rules to consider when using subqueries –
A subquery must be enclosed in parentheses.
The subquery must be placed on the right side of the comparison operator.
Subqueries cannot manipulate their results internally, therefore, ORDER BY clause cannot be added into a subquery.
You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.
Use single-row operators with single-row subqueries.
If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
Type of Subquery in SQL
Subqueries are two types-
Single row subquery : Returns zero or one row and use single-row comparison operator only.
Multiple row subquery : Returns one or more rows and use multi Row comparison operators.
Subqueries may be nested. Subqueries are placed within another subquery.
We can use subqueries with UPDATE, INSERT, and DELETE statements as well. We will learn about these in coming tutorials.