Subquery

What is Subquery? Explain the Properties of a Subquery?
A query which is nested within another query is called subquery.
If you want to fetch data from one table based on the input value of other table we write subqueries.
Here inner query is executed first, output will be passed to outer query, based on that outer query will be executed. To write a subquery we need a common column.
Table from which you expect output, write an outer query on that.
Table from which you expect input, write an inner query on that.

Two types of subqueries :
- Single row subquery: Single-row subqueries are used with Comparison Operators.
- Multi row subquery: Multiple-row subqueries are used with Logical Operators.

Single row subquery :
If the subquery generates single output value then its called Single row subquery.
equal to operator is used in the single row subquery.

Syntax :
Select dname from dept where deptno = (select deptno from emp where empno = 7654);


Multi row subquery :
If the subquery generates more than one output value then its called Multi row subquery.
In operator is used in Multi row subquery.

Syntax :
Select * from emp where deptno IN (Select deptno from dept where dname IN ('Accounting', 'Research'));

SELECT * FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP);