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
-
.png "Sea battle game only using HTML and CSS")
Sea battle game only using HTML and CSSAdmin
sea battle game where using only HTML and CSS and animation in css and html tags sea battle game2021-09-20 03:43:36
Comments (8)
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
Leave a Comment