Some New Books – bring on the learning…


I bought two new books today: Inside Microsoft SQL Server 2005: T-SQL Querying and Inside SQL Server 2005: T-SQL Programming, both by Itzik Ben-Gan. I spent a morning in one of his Black Hat SQL sessions a few years ago and have been following his works ever since.

I’ve started with T-SQL Querying as it’s described by the author as Part 1 and the other book Part 2. 11 pages in and I’ve found out something small, yet helpful.

Have you ever been confused with regards to whether to place a logical expression in the ON filter or the WHERE filter, e.g.

SELECT C.customerid
FROM dbo.Customers AS C
JOIN dbo.Orders AS O
ON C.customerid = O.customerid
WHERE C.city = ‘Madrid’

vs –>

SELECT C.customerid
FROM dbo.Customers AS C
JOIN dbo.Orders AS O
ON C.customerid = O.customerid
AND C.city = ‘Madrid

In this case it makes no difference. The difference appears though when you use an OUTER JOIN. As explained in the book, the ON is processed first and then the WHERE. So the first statement is suitable if we only want data for Madrid, however if we want OUTER rows maintained, then the second query is suitable.

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