|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
WHERE salary > (SELECT avg(salary) FROM employees);
Example 2 of Scalar subquery
Select dname, (select count(*)
where emp.deptno= dept.deptno) cnt
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
WHERE salary = (Select MAX(salary) FROM employees);
Example of Multiple Row Subquery
SELECT employee_id, last_name, salary
WHERE salary IN (SELECT Min(salary)
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
where dept_id =30 and salary >=ALL (Select salary
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
where (job_id, salary) in (select job_id, salary
where employee_id =420);