Tuesday, February 9, 2010

Dear FROM Clause...

This blog entry is participating in T-SQL Tuesday #003, hosted this month by Rob Farley. You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: Relationships.

Now please sit back and relax, get some tissue, and read on…



Dear John...Dear FROM Clause,

I know that Valentine’s Day is less than a week away, but I felt it was important to write you this letter… I had to put my feelings down on paper. I don’t know quite how to tell you this, but… well… our relationship cannot continue.

It’s just too confusing and complex, and there have been times when it has become… dare I say it?… dangerous.

I still think back to when we were first introduced and we went to NorthWind and AdventureWorks together and started to do SELECTs.

use AdventureWorks
select FirstName,LastName
from Person.Contact
Those were fun times… we’d just hang out with your pals WHERE and ORDER BY and sometimes GROUP BY would tag along... there were always a lot of laughs when HAVING showed up… he’s a riot! Occasionally CROSS APPLY would come too… I had a little trouble understanding him at first, but now I think he’s the coolest guy around! I’ve written about him in my online diary several times.

And of course, your table friends would always JOIN the party… and sometimes it would be quite a wild party:

/* 
Suggest 3 alternate Clothing/Accessory Products
to the @CustomerID that (s)he has never bought before
when (s)he is looking at a particular @ProductID.
(Customers who bought this item also bought...)
See bradsruminations.blogspot.com/2009/07/customers-who-bought-this-item-also.html
*/
use AdventureWorks
select top (3) SuggProdID=d2.ProductID
from Sales.SalesOrderDetail d1
join Sales.SalesOrderHeader h1 on d1.SalesOrderID=h1.SalesOrderID
join Sales.Customer c on h1.CustomerID=c.CustomerID
join Sales.SalesOrderHeader h2 on h1.CustomerID=h2.CustomerID
join Sales.SalesOrderDetail d2 on h2.SalesOrderID=d2.SalesOrderID
join Production.Product p on d2.ProductID=p.ProductID
join Production.ProductSubCategory sc on p.ProductSubCategoryID=sc.ProductSubCategoryID
where d1.ProductID=@ProductID
and h1.CustomerID<>@CustomerID
and c.CustomerType='I'
and p.ProductSubCategoryID<>(select ProductSubCategoryID
from Production.Product
where ProductID=@ProductID)
and sc.ProductCategoryID in (3,4)
and d2.ProductID not in (select ProductID
from Sales.SalesOrderDetail sod
join Sales.SalesOrderHeader soh
on sod.SalesOrderID=soh.SalesOrderID
where CustomerID=@CustomerID)
group by d2.ProductID
having count(distinct h2.SalesOrderID)>5
order by count(distinct h2.SalesOrderID) desc
We were just friends during those times… it was relaxing and fun and there was no pressure. And then…

You asked me out on an UPDATE.

It was a little awkward at first, but after our second and third UPDATEs (and beyond), I started to feel a little more comfortable as we got to know each other better.

/* 
Howard Snyder's company changed its name.
We updated it in the Customers table, so now we need to
update the ShipName in all the company's unshipped orders.
*/
use NorthWind
update Orders
set ShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
Then you wanted to have more variety in our UPDATEs… it started to become a little overwhelming and I had to admit, I was starting to get a little confused with our relationship.

/*
The query plans for all following updates
are equivalent in every way
*/
update Orders
set ShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null

update Orders
set ShipName=Customers.CompanyName
from Customers /* No Orders table in FROM clause */
where Customers.CustomerID=Orders.CustomerID /* But connected via WHERE */
and Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null

update o /* Update alias of the FROM clause */
set ShipName=c.CompanyName
from Orders o
join Customers c on o.CustomerID=c.CustomerID
where c.ContactName='Howard Snyder'
and o.ShippedDate is null

update Orders /* Note the alias in the FROM clause is ignored here */
set ShipName=c.CompanyName
from Orders as SomeObnoxiousAlias
join Customers c on SomeObnoxiousAlias.CustomerID=c.CustomerID
where c.ContactName='Howard Snyder'
and SomeObnoxiousAlias.ShippedDate is null
I started to get worried, because sometimes it seemed as if our relationship wasn’t going anywhere specific… there was no direction… no specific goals… it was kind of non-deterministic.

/*
Which of ERNSH's 30 orders will end up
updating his Country column?
We really have no idea.
*/
update Customers
set Country=Orders.ShipCountry
from Customers
join Orders on Customers.CustomerID=Orders.CustomerID
where Customers.CustomerID='ERNSH'
And then eventually our relationship moved to the next level.

We started… well, y’know (blush)… doing it… the D-Word… DELETEing.

At first it was kind of exciting but really scary at the same time. And I have to admit that I felt a little guilty about all these new-found feelings of power awakening inside me.

But then you started to come on too strong… you were smothering me… it was like you were there all the time

/* Two FROMs in a DELETE is kind of confusing */
delete from Customers
from Customers
where CustomerID='FISSA'
I needed some space… And I asked you to pull back and not let things go so fast…

/* But the first FROM is optional */
delete Customers
from Customers
where CustomerID='FISSA'

/* And FROM, of course, is not needed if there's only one table */
delete Customers
where CustomerID='FISSA'
Then things got weird. You wanted to get into all this… experimentation. We starting DELETEing in ways that were really unsettling and… unnatural. My SQL Server MVP friends Steve Kass and Aaron Alton warned me about you, but I didn’t listen. It was becoming dangerous, and we started doing things we really shouldn’t be doing… and then one thing would lead to another, and… well I still feel great pangs of guilt and sorrow when I think back on how I had to sometimes abort the query to avoid undesirable consequences.

/*
The following will delete all of FISSA's orders.
Potentially confusing.
*/
delete Orders
from Orders Customers
where CustomerID='FISSA'

/*
The following will delete the Customer named FISSA.
Really confusing? Yep.
*/
delete Orders
from Customers Orders
where CustomerID='FISSA'

/*
OOPS! The following will end up deleting ALL Order Detail rows.
Notice there's no relationship between the table whose rows we're
deleting and the table referenced in the FROM clause.
As long as there is one single order in the Orders table
with a CustomerID of ERNSH, the Order Details table will be
completely cleared out... *Poof*... Nothing... Nada... Zip.
*/
delete [Order Details]
from Orders
where CustomerID='ERNSH'
And so I’ve come to the decision that we can no longer have a DELETE relationship… ever again. I’m sorry it has to be this way, but I need to have more control of my life. If I have the urge to DELETE, I will just have to be more careful and use protection… I’ll always use EXISTS or IN… our friend WHERE can get them for me.

/*
Delete all Order Details rows that belong
to the Customer ERNSH.
The two queries below are equivalent in every way.
*/
delete [Order Details]
where exists (select *
from Orders
where OrderID=[Order Details].OrderID
and CustomerID='ERNSH')

delete [Order Details]
where OrderID in (select OrderID
from Orders
where CustomerID='ERNSH')
Things won’t be so confusing anymore… my DELETE life will be more straightforward and easier to understand and… well… normal.

/*
Delete all SalesOrderDetail rows that satisfy all of the following:
1) The order was placed by a guy named Richard with a SuperiorCard Credit Card
2) The line item involved a Special Offer of the Reseller Category
3) The line item involved a Product of the Accessories Category
*/
use AdventureWorks
delete Sales.SalesOrderDetail
where SalesOrderID in (select SalesOrderID
from Sales.SalesOrderHeader soh
join Person.Contact c on soh.ContactID=c.ContactID
join Sales.CreditCard cc on soh.CreditCardID=cc.CreditCardID
where c.FirstName='Richard'
and cc.CardType='SuperiorCard')
and SpecialOfferID in (select SpecialOfferID
from Sales.SpecialOffer
where Category='Reseller')
and ProductID in (select ProductID
from Production.Product p
join Production.ProductSubCategory ps
on p.ProductSubCategoryID=ps.ProductSubCategoryID
join Production.ProductCategory pc
on ps.ProductCategoryID=pc.ProductCategoryID
where pc.Name='Accessories')
I’m also considering ending our UPDATEing relationship. I’m becoming more involved in a query organization called CTE and that’s helping me move into another direction.

use NorthWind;
with TargetRows as
(
select ShipName
,NewShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
)
update TargetRows
set ShipName=NewShipName
I realize that you will still be peripherally involved in my UPDATEing and DELETEing, watching from the sidelines, but I’ll just have to accept that.

To be brutally honest, I’ve considered breaking off from you completely, but that’s just not possible… We still have to work together, and I’m not about to go out and get another job just to avoid you.

I hope you understand all the things I’ve written in this letter. I’m sure it’s hard reading something like this, but I think over time you will understand and accept my decision.

I’m sure we can still be friends.

Sincerely,

Every SQL Developer in the Universe (I hope)

9 comments:

  1. You have a very dirty mind Brad! I will catch with you tomorrow at SFO UG.

    ReplyDelete
  2. Well put, and thanks.

    BTW Brad, you may have noticed that I don't have an entry for T-SQL Tuesday on my own blog. I had meant to spend my free time last night writing something, but (ironically) decided in the interest of domestic harmony to focus on my own relationship.

    ReplyDelete
  3. @Sankar: Thanks for your comment, though I have absolutely no idea what you meant by "dirty mind". ;) I'm afraid I'm not going to the San Francisco UG tomorrow... the same topic will be presented at the Mountain View UG next week, which I'm planning to attend.

    @Michael: I almost didn't post anything myself. I had no idea what to write about for the last week, and then some goofy light bulb went off late yesterday... thought about it last night... and slammed it out this morning. (Let's hear it for domestic harmony!)

    ReplyDelete
  4. Pure-d awesomeness :)

    Golf clap, sir....

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Brad,
    Nice SQL Love Letter ... :)

    ReplyDelete
  7. This is A-W-E-S-O-M-E, an absolute masterpiece. I have to say, I'm VERY impressed. I think you might be taking your place alongside the great poets soon!

    ReplyDelete
  8. Well. I guess I shouldn't be surprised at that someone like you would do something like this. No matter. I have a new lover, LINQ. LINQ puts me first where FROM belongs. No more waiting behind SELECT and all those columns and messy expressions. At least LINQ has the intellisense to realize my proper place.

    So long...

    ReplyDelete
  9. @FROM Clause:

    Fine... whatever... I hope you're very happy with this "Mod Squad" guy LINQ.

    (To whomever: LOL! ROFL! Bravo! Encore! Thanks for a great laugh this morning).

    ReplyDelete