0

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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Henri Sula
  • 41
  • 6

1 Answers1

0

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”.

user3163495
  • 2,425
  • 2
  • 26
  • 43
  • in the third query isnt b.color always the same as a.color since is the same table basically? – Henri Sula Feb 11 '23 at 16:02
  • @HenriSula You may be getting this confused with a sql JOIN--subqueries are similar to JOINs, but they aren't exactly the same. B.COLOR is set to whatever A.COLOR is for each record in the "outer" query, so B.COLOR will change for each record (and thus the subquery will return a different value for AVGPOP). – user3163495 Feb 11 '23 at 16:10
  • @HenriSula In the third query, the subquery is ran *for each row* in the outer query, so if there are 1,000 birds in the BIRDS table, the subquery will run 1,000 times, once for each bird. – user3163495 Feb 11 '23 at 16:11
  • @HenriSula In the second query example, however, the subquery is run **only once**, because we are using it in place of a table, so it doesn't need to be run more than once. – user3163495 Feb 11 '23 at 16:13
  • im getting confused in the third query because everywher i read ,they tell you that the inner query is executed first – Henri Sula Feb 11 '23 at 16:35
  • @HenriSula The inner query is executed first **only** if it is used in place of a table, like in my second query example. If the subquery is used as a column, like in my third query example, then it is *not* executed first. The tutorials you are reading may not be explaining with enough detail. – user3163495 Feb 11 '23 at 16:50
  • so the outer query will define how many rows the results set will have ,in your query if i get it right the result set will be every row in the table birds will be displayed attached with the avg population of all birds that have the same color , so you are trying to find where a.color matches b.color basically , the outer query will fix a.color on the first record and the subquery will compare a.color on the first record will all the other records on b.color ? – Henri Sula Feb 13 '23 at 18:44