SUBQUERY or NESTED Query or INNER Query are query within SQL queries
Syntax:
There are no specific syntax but you can prepare subqueries with different usage of SQL CLAUSES
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME from TABLE_NAME WHERE ..);
Example
Select third highest salary from employees table:
SELECT TOP 1 salary
FROM
(SELECT TOP 3 salary
FROM employees
ORDER BY salary DESC) AS Comp
ORDER BY salary ASC
Alternate query :
a)
SELECT * FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 2;
b)
SELECT salary
FROM
(SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 3) AS Comp
ORDER BY salary
LIMIT 1;
c)
SELECT Max(salary)
FROM employees
WHERE salary < (SELECT Max(salary)
FROM employees
WHERE salary NOT IN(SELECT Max(salary)
FROM employees))
d)
SELECT *
FROM Employees Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employees Emp2
WHERE Emp2.Salary > Emp1.Salary);
Here replace ‘N’ with 3 while running the query
Output:
| emp_id | emp_name | hire_date | salary | dept_id |
|---|---|---|---|---|
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
Learn more : SQL Tutorial , SQL Interview Questions