19
Mar
10

Chapter 7 — Pulling Apart the From Clause

As I was writing this blog post this evening my three-year old daughter asked me what I was doing… I told her that I was working on “pulling apart the from clause”, she notified me that “I should only do that during the day time”.  It’s an apropos statement considering the light that Rob Farley sheds on one of the more fundamental and oft misunderstood element of t-sql.

The FROM clause is clearly necessary for most queries in t-sql.  One of the best talks that I’ve ever done was for the SSWUG v-Conference and I spent some time explaining the difference between Inner Join, Outer Join, Full Outer Join and Cross Joins.  The feedback that I received from that segment of the session was more than the other two sessions that I gave during that conference, combined.  It’s interesting that as I have been working with SQL Server for many years, I often lose sight of things that were difficult for me to learn early on.  The idea of combining an outer join, an inner join and yet another inner or outer join in one query was not easy for me to wrap my head around when I was new to relational databases.  So I fully appreciate the great work that Rob put together in this chapter.  It’s a very easy and fast read which is meant as a huge compliment to the time he spent thinking through how this subject could make the most sense.  Heck, I liked it so much, I’m going to lend the book to my Mom who is always asking me what I do and is getting tired of me trying to explain to her what I do. 

Back when SQL Server 2000 was in its prime I ran into a query that had over 120 join statements in it… The reason that it came to my attention was that it was so huge to compile that SQL Server (on 32 bit at the time) actually ran out of MEMORY while it attempted to create the query plan for it.  That was a fun excercise.  I learned a great deal about 32 bit memory architecture in the ensuing days and I also learned a great deal about SQL Server.  Upon opening a case with MSFT, they told me that there were two options for this beast of a query.  1.) Re-write it.  2.)  Buy new hardware that could support 64 bit, re-install the OS, SQL Server etc… and migrate the environment to 64 bit.  *sigh*.   It was a very long week but that query was re-written and in the process I learned how to eradicate unnecessary joins as Rob explains in the chapter.  More importantly I learned that even though it can be done, that doesn’t mean it should be done. 

Keeping that last thought in mind, what can be done, not what should be done, I offer one of the items that I’ve seen over the years, the usage of derived tables in join statements.  A quick example of this would be:

SELECT EMP.EmployeeID, Emp.FirstName, Emp.LastName
 FROM Employee EMP JOIN
            (Select EMP2.FirstName, EMP2.LastName from Employee EMP2 Group By FirstName, LastName having count (*) > 1) EMP2
 ON EMP.FirstName = EMP2.FirstName AND EMP.LastName = EMP2.LastName

That query creates what’s known as a derived table (notice the Select statement in the From clause of the query).  This can be helpful at times when dealing with aggregations or as demonstrated above, finding those employees who either have the same name or are in the employee table twice. 

Thanks Rob for putting together a great chapter on the FROM clause.  While I don’t know the original author of the query that I had the joy of re-writing, I do hope that person knows that I had to re-write it and has taken the time to read your excellent chapter.

Advertisements

1 Response to “Chapter 7 — Pulling Apart the From Clause”


  1. March 19, 2010 at 4:05 am

    Thanks Jeremy! I’ve just found myself feeling very flattered by reading three nice reviews of my first chapter. 🙂 I’ll look forward to reading the stuff about chapter 40.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers