Tuesday, September 22, 2009

SQL Funny Business

Today at work we were talking about mixing old (table, table where join criteria) and new (table join table on join criteria) style joins in the same query. So that got me to thinking about what ON really does. I ran the following queries against the infamous Northwind Database:

-- The modern style join:
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON od.OrderID = o.OrderID 
WHERE o.CustomerID = 'SUPRD'

-- The classic style join:
SELECT *
FROM dbo.Orders o, [Order Details] od
WHERE od.OrderID = o.OrderID
 AND o.CustomerID = 'SUPRD'

-- Inverted modern join (join criteria in WHERE, selection criteria in ON):
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON o.CustomerID = 'SUPRD'
WHERE od.OrderID = o.OrderID

All three queries gave me the same results set.

Is the ON clause just a different place to shove selection criteria (a phantom where). I like the modern style. You can put all the join information together. You could mix things up when you practice Job Security Based Programming.

I think it would be fun to have a obscure SQL programming competition, like the obscure C programming competitions in the days of yore.