Single Row Subquery
Single row subquery is a subquery that returns zero or one row to the outer SQL statement. It is used with comparison operators such as =, <,>, >=, <=, <>, IN, BETWEEN, etc. The subquery must be enclosed in parentheses.
Single row subquery can be used in SELECT, WHERE, and FROM clauses.
Example:
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 50000 |
| 2 | Jane | Doe | 60000 |
| 3 | Mike | Smith | 70000 |
| 4 | David | Johnson | 80000 |
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
| first_name | last_name | salary |
|---|---|---|
| Mike | Smith | 70000 |
| David | Johnson | 80000 |
In the above example, the subquery returns the average salary of all employees. The main query returns the first name, last name, and salary of employees whose salary is greater than the average salary.
Multiple Row Subquery
Multiple row subquery is a subquery that returns more than one row to the outer SQL statement. It is used with comparison operators such as IN, ANY, ALL, etc. The subquery must be enclosed in parentheses.
Multiple row subquery can be used in SELECT, WHERE, and FROM clauses.
Example:
| employee_id | first_name | last_name | department_id |
|---|---|---|---|
| 1 | John | Doe | 1 |
| 2 | Jane | Doe | 2 |
| 3 | Mike | Smith | 1 |
| 4 | David | Johnson | 3 |
SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');
| first_name | last_name |
|---|---|
| John | Doe |
| Mike | Smith |