Subqueries in  SQL | SQL Queries -P4

SQL Queries (Microsoft SQL server), today we see the most commonly used SQL queries in our daily developer’s life. So you must go through these queries and remember them.

Uses of 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 Manager.Id, Employees.Salary 
FROM ( SELECT Id 
FROM Employees 
WHERE ManagerId IS NULL 
) AS Managers 
JOIN Employees ON Managers.Id = Employees.Id 

How to use Subquery in the SELECT clause?

SELECT Id,
FName,
LName,
(SELECT COUNT(*) FROM Cars WHERE Cars.CustomerId = Customers.Id) AS NumberOfCars
FROM Customers

Define 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)

Define Correlated Subqueries | SQL Queries

Correlated (also known Synchronized or Coordinated) Subqueries are nested queries that make references to the current row of their outer query:

SELECT EmployeeId
FROM Employee AS eOut
WHERE Salary > ( SELECT AVG(Salary)
FROM Employee eInn
WHERE eInn.DepartmentId = eOut.DepartmentId
)

Define Subqueries in the 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, pop1000
FROM cities WHERE pop1000 < (SELECT avg(pop1000) FROM cities);

How do Filter query results using a query on a different table?

This query selects all employees, not on the testers table.

SELECT *
FROM Employees
WHERE EmployeeID not in (SELECT EmployeeID FROM Testers)

Define Subqueries in FROM clause.

SELECT * FROM
(SELECT city, temp_hi - temp_lo AS temp_var FROM weather) AS w
WHERE temp_var > 30;

Read Also: Commonly used SQL Queries

Source: Check