SQL Subqueries

Subquery :
subquery is a SQL query within a query

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

SELECT EmployeeId FROM Employee AS eOuter WHERE Salary > (SELECT AVG(Salary) FROM Employee eInner WHERE eInner.DepartmentId = eOuter.DepartmentId)

Filter query results using query on different table
Example:
This query selects all employees not on the Supervisors table.
SELECT * FROM Employees WHERE EmployeeID not in (SELECT EmployeeID FROM Supervisors)
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
Example:
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;

Subqueries in SELECT clause
Example:

SELECT w.*,  (SELECT c.state FROM cities AS c WHERE c.name = w.city ) AS state FROM weather AS w;