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 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)