SUBQUERY
• A subquery in SQL is a query nested inside another query.
• It is used to perform operations that depend on the result of another query.
• Subqueries can be placed in various parts of a SQL statement, such as in the SELECT, FROM,
WHERE or HAVING.
OUTER QUERY
Output of the inner query
INNER QUERY
Input of the outer query
Syntax:
Select column _name from table _name where column _name comparison _operator (select
Column _name from table _name where condition);
TYPES OF SUBQUERY
• Single-row subquery
• Multi-row subquery
Single-row subquery
Returns a single row. used with comparison operators like =, >, < ….
Syntax:
Select column _name from table _name where condition;
Select column _name from table _name where column _name comparison _operator (select
Column _name from table _name where condition);
example:
SELECT name FROM employees WHERE salary > (SELECT name FROM employee where id=‘3’);
Multi-row subquery
Returns multiple rows. Used with operators like IN, ANY, or ALL.
Syntax:
SELECT column _name FROM table _name WHERE column _name operator (SELECT column _name
FROM table _name WHERE condition);
SELECT * FROM table _name WHERE column _name operator (SELECT column _name FROM table
_name WHERE condition);
Example:
SELECT name FROM employees WHERE department _id IN (SELECT department _id FROM
departments WHERE location = 'New York');