Sql Subqueries

Sql Subqueries
Nikhil
Published on 2021-08-15 06:17:44

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 (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website