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
WHERE ManagerId IS NULL
) AS Managers
JOIN Employees ON Managers.Id = Employees.Id
Subquery in SELECT clause
(SELECT COUNT(*) FROM Cars WHERE Cars.CustomerId = Customers.Id) AS NumberOfCars
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.
WHERE Salary = (SELECT MAX(Salary) FROM Employees)
Correlated (also known as Synchronized or Coordinated) Subqueries are nested queries that make references to the current row of their outer query:
FROM Employee AS eOuter
WHERE 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.
WHERE EmployeeID not in (SELECT EmployeeID
The same results can be achieved using a LEFT JOIN.
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:
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:
San Francisco 776733
ST LOUIS 348189
Kansas City 146866
ATutorialHub Related Guide
AWS Amazon EC2-Deploy a MERN Stack application to AWS Amazon EC2
Sea battle game only using HTML and CSS
panduranga gupta2021-07-05 07:03:13
good website for learning and help me a lot
The awsome website i am looking like for a long time, good work atutorialhub team keep doing
Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience
It is very helpful to students and easy to learn the concepts
Great job Tutorials are easy to understand Please make use of it
Zain Khan2021-09-18 15:07:23
Great content and customized courses.
Rudrakshi Bhatt2021-09-09 15:08:10
Well structured coursed and explained really well!
Pavana Somashekar2021-09-11 15:09:08
Good platform for beginners and learn a lot on this website