Sql Subqueries

SQL Subquery in FROM clause
A subquery in a FROM clause acts similarly to a temporary table that is generated during the execution of a query and lost afterward.
SELECT Managers.Id, Employees.Salary
FROM (
SELECT Id
FROM Employees
WHERE ManagerId IS NULL
) AS Managers
JOIN Employees ON Managers.Id = Employees.Id
Subquery in SELECT clause
SELECT
Id,
FName,
LName,
(SELECT COUNT(*) FROM Cars WHERE Cars.CustomerId = Customers.Id) AS NumberOfCars
FROM Customers
Subquery in WHERE clause
Use a subquery to filter the result set. For example, this will return all employees with a salary equal to the highest-paid employee.
SELECT *
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees)
Correlated Subqueries
Correlated (also known as Synchronized or Coordinated) Subqueries are nested queries that make references to the current row of their outer query:
SELECT EmployeeId
FROM Employee AS eOuter
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee eInner
WHERE eInner.DepartmentId = eOuter.DepartmentId
)
Subquery SELECT AVG(Salary) ... is correlated because it refers to Employee row eOuter from its outer query.
SQL Filter query results using query on a different table
This query selects all employees, not on the Supervisors table.
SELECT *
FROM Employees
WHERE EmployeeID not in (SELECT EmployeeID
FROM Supervisors)
The same results can be achieved using a LEFT JOIN.
SELECT *
FROM Employees AS e
LEFT JOIN Supervisors AS s ON s.EmployeeID=e.EmployeeID
WHERE s.EmployeeID is NULL
Subqueries in FROM clause
You can use subqueries to define a temporary table and use it in the FROM clause of an "outer" query.
SELECT * FROM (SELECT city, temp_hi - temp_lo AS temp_var FROM weather) AS w
WHERE temp_var > 20;
The above finds cities from the weather table whose daily temperature variation is greater than 20. The result is:
city temp_var
ST LOUIS 21
LOS ANGELES 31
LOS ANGELES 23
LOS ANGELES 31
LOS ANGELES 27
LOS ANGELES 28
LOS ANGELES 28
LOS ANGELES 32
.
Subqueries in WHERE clause
The following example finds cities (from the cities example) whose population is below the average temperature (obtained via a sub-query):
SELECT name, pop2000 FROM cities
WHERE pop2000 < (SELECT avg(pop2000) FROM cities);
Here: the subquery (SELECT avg(pop2000) FROM cities) is used to specify conditions in the WHERE clause. The result is:
name pop2000
San Francisco 776733
ST LOUIS 348189
Kansas City 146866
ATutorialHub Related Guide
Comments (9)
User Comments

panduranga gupta
2021-07-05 07:03:13good website for learning and help me a lot

raju
2021-09-25 14:58:47The awsome website i am looking like for a long time, good work atutorialhub team keep doing

Shivani
2021-09-01 15:03:56Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

Harshitha
2021-09-10 15:05:45It is very helpful to students and easy to learn the concepts

Sowmya
2021-09-14 15:06:41Great job Tutorials are easy to understand Please make use of it

Zain Khan
2021-09-18 15:07:23Great content and customized courses.

Rudrakshi Bhatt
2021-09-09 15:08:10Well structured coursed and explained really well!

Pavana Somashekar
2021-09-11 15:09:08Good platform for beginners and learn a lot on this website

Sax
2021-09-25 19:35:50Nice website
Leave a Comment