SQL Scalar subquery

SQL inline View subquery

SQL Correlated subquery

SQL Subqueries

A scalar subquery returns a single row and a single column value.

Example 1 of Scalar subquery

SELECT last_name, job_id, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);

Example 2 of Scalar subquery

Select dname, (select count(*)
             from emp
             where emp.deptno= dept.deptno) cnt
from dept

SQL Subqueries can be single row, multiple rows, single column and multiple columns.

You can use single-row subqueries with single-row operators and multiple-row subqueries with multiple-row operators.

Example of Single Row Subquery

Find the highest salary :

SELECT last_name, salary
FROM employees
WHERE salary = (Select MAX(salary) FROM employees);

Example of Multiple Row Subquery

SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT Min(salary)
               FROM employees
               GROUP BY department_id);

Example of single column subquery

A query to retrieve the name of highest paid person in the department 30

Please see that ALL is used with comparison operator because subquery is not returning single value, It is returning multiple values.

Select first_name, last_name
from employee
where dept_id =30 and salary >=ALL (Select salary
                                    from employee
                                    where dept_id = 30);

Example of Multiple column subquery

A subquery that compares more than just one column between the parent query and the subquery is called multiple-column subquery.

Extract employees that make the same salaries as other employee with employee_id 420 with the same job

Select employee_ID, last_name, job_id, salary
from employees
where (job_id, salary) in (select job_id, salary
                           from employees
                           where employee_id =420);