Advancing Knowledge of Subqueries
-
I am currently reading through Itzik-Ben-Gans Microsoft SQL 2008 T-SQL Fundamentals book and I've made sense of it so far. I'm completely lost in subqueries. I get the general basis of this information, but understanding the logic behind this type of code eludes me:
Select orderid, orderdate, empid, custid,
(Select MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.orderid < O1.orderid) AS Prevorderid
FROM SALES.ORDERS AS O1;
This will display the order id that goes before the current order id in the new column prevorderid. Why? If someone could produce some type of pseudo code or explain to me why this subquery functions the way it does I would be very appreciative. Thank you very much.
-
Think of the subquery (O2) without the WHERE clause as a standalone query for a minute. Consider what it'll do by itself. It'll return the MAX(OrderID) from sales.orders. Now, think out the outer query (O1) by itself but without the subquery (O2). It will return the rows from sales.orders and nothing more.
When you join the two queries together and correlate them to one another with the WHERE clause, the inner query behaves a little differently. It's going to return one row for each row in the outer query - the MAX(OrderID) where the OrderID is less than the OrderID of the outer query. The result is being SELECTed in the outer query and aliased as Prevorderid, so it will display as another field in the result set. The "for each row" portion may be the part that was throwing you for a loop.
Does this make any sense? I hope I didn't confuse the situation any more.
-
Select orderid, orderdate, empid, custid,(Select MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.orderid < O1.orderid) AS Prevorderid
FROM SALES.ORDERS AS O1;
This is how I understand it:
We have a query where one table joins to itself so to visualise this I have created a mini version of the orders table O1 and O2 as below:
and it goes something like this:
1) O1.OrderID = 1, is there another ID in the same table O2 that is lower? NO
2) O1.OrderID = 2, is there another ID in the same table O2 that is lower YES! its OrderID 1
3) O1.OrderID = 3, is there another ID in the same table O2 that is lower? YES OrderIDs 1 & 2 so which do we want? The MAX of these two so it's 2
4) OrderID = 4, is there another ID in the same table O2 that is lower? YES, 1, 2, 3 so which do we want? The MAX of these so it's 3
.. and so on.
That's what the nested part of the query is doing
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
wow guys thank you. I get it! muhahaha
-
Ryan1 (9/3/2013)
wow guys thank you. I get it! muhahahaWhat's funnier is that once you get through this book + plenty of practice, you'll start to see these joins in your head 😛
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (9/3/2013)
Ryan1 (9/3/2013)
wow guys thank you. I get it! muhahahaWhat's funnier is that once you get through this book + plenty of practice, you'll start to see these joins in your head 😛
That's very true. The joins themselves will start to become second-nature and it'll be the approach that you'll think about more and more.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply