A subquery is a complete SELECT nested inside another query, and it can appear in several different positions depending on what it returns:
-- Scalar subquery (returns exactly one value): usable anywhere a single value is expected
SELECT * FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
-- Column subquery (returns one column, many rows): usable with IN, ANY, ALL
SELECT * FROM Employees
WHERE dept IN (SELECT dept FROM Departments WHERE dept <> 'Finance');
-- Table subquery (a full result set, aliased and queried like a table)
SELECT sub.dept, sub.avg_salary
FROM (
SELECT dept, AVG(salary) AS avg_salary
FROM Employees
GROUP BY dept
) AS sub
WHERE sub.avg_salary > 55000;
A subquery used in the FROM clause (the third form above, sometimes called a derived table) must be given an alias — this is a hard syntax requirement in standard SQL, not a style preference, because the outer query needs a name to refer to the derived table's columns by.