Page 196 - SQL

weather table columns with the corresponding states from the cities table.

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

Filter query results using query on 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

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.

Read Subqueries online: https://riptutorial.com/sql/topic/1606/subqueries

https://riptutorial.com/ 178