SQL queries, can't figure out how this query can work

Question

How can you select max(table column), and on the from clause declare another table column? It doesn't make any sense - can anyone explain?

SELECT 
    Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)        
     FROM Sales.SalesOrderDetail AS OrdDet         
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM 
    Sales.SalesOrderHeader AS Ord;

Answer 1

A sql SELECT query always returns what’s called a “recordset”, no matter how simple or fancy the query is. For example, this will return a “recordset” with 2 columns and however many rows are in the table BIRDS matching the name “Bluebird” or “Cardinal”:

SELECT NAME, COLOR FROM BIRDS WHERE NAME = ‘Bluebird’ OR NAME = ‘Cardinal’

Now, if you wanted to further narrow down what you’re querying the database for, sql lets you “query the query”, if that’s what you want to do:

SELECT * FROM (SELECT NAME, COLOR FROM BIRDS WHERE NAME = ‘Bluebird’ OR NAME = ‘Cardinal’) WHERE COLOR = ‘Blue’ OR COLOR = ‘Yellow’

^^^ This second query uses the recordset from the first query in place of a table.

This “inner query” is called a “subquery”.

You can also use subqueries in individual columns, like so:

SELECT A.NAME, (SELECT AVG(POPULATION) FROM BIRDS B WHERE B.COLOR = A.COLOR) AVGPOP FROM BIRDS A

^^^ This third query uses a subquery to fetch the average population of birds matching the color of each bird returned in the “outer” query. So, the final recordset returned will have two columns: “NAME” and “AVGPOP”.