Deprecated: Function split() is deprecated in /home/tbriggs/public_html/s9y/plugins/serendipity_event_metadesc/serendipity_event_metadesc.php on line 101

Confessions of an Old SQL Hacker: I Like ANSI Joins

For longer than I care to remember, I've been writing joins like so:

SELECT col1, col2 -- Boo on "SELECT *"!
FROM T1, T2
WHERE T1.x = T2.x

Over the last six months or so, however, I've found myself forced to used outer joins (which are evil, but that's another story) and thus having to write queries like this:

SELECT col1, col2 -- Seriously, "SELECT *" is for rookies
FROM T1
LEFT OUTER JOIN T2 ON(T1.x = T2.x)

Then, to keep things consistent (think self-Romanism) inner joins obviously get written like this:

SELECT col1, col2 -- Did you really think I was going to use "SELECT *"?
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)

What's shocking about this is that I find myself liking this syntax. It's much clearer, at least in my opinion, especially when joining more than two tables or when there's more than one join criteria between two tables. Further, it separates the join criteria from the "filter" criteria, i.e. what's in the WHERE clause is only what belongs in the WHERE clause. Consider:

SELECT col1, col2 -- You get the point
FROM T1, T2, T3, T4
WHERE T1.x = T2.x
AND T2.x = T3.x
AND col3 = 'foo'
AND T3.x = T4.x
AND T2.y = T3.y

vs.

SELECT col1, col2 -- I hope you do anyway
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)
INNER JOIN T3 ON(T2.x = T3.x AND T2.y = T3.y)
INNER JOIN T4 ON(T3.x = T4.x)
WHERE col3 = 'foo'

See the difference? Join criteria are forced to be grouped together logically, and the WHERE clause is cleaner. It's hard not to like it.

And it beats the hell out of the old Oracle outer join syntax. 'nuf said on that subject - you either know what I'm talking about or wouldn't believe that I was quoting real syntax. Anyway...

There you have it. I like the ANSI join syntax. I admit it.

I guess you can teach an old dog new tricks.
Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments
Welcome to 1992
In my defense, Oracle didn't support this syntax until like 2002, so... as usual, I'm a decade behind. :-P
ANSI sucks and is less declarative
look at this nightmare
if you dont agree, you should not be writing a blog
(i had such hope for you)
i had to re-write it just to understand it
first you have to unroll the embedded levels...
(this is the way access does things....wrong, wrong, wrong)

SELECT "SCCalls"."AddressStreet", "SCWorkOrders"."WorkOrderNumber", "ARCustomers"."CustomerName", "SCCalls"."Date", "SCCalls"."Description", "SCEquipments"."EquipmentNumber", "ShAgents"."PrefFullName", "SCCalls"."CallNumber", "SCCalls"."BillCodeID", "SCCalls"."CloseDate", "SCCalls"."Notes"
FROM ((("Cofinal"."dbo"."SCCalls" "SCCalls" INNER JOIN "Cofinal"."dbo"."SCWorkOrders" "SCWorkOrders" ON "SCCalls"."WorkOrderID"="SCWorkOrders"."WorkOrderID") INNER JOIN "Cofinal"."dbo"."SCEquipments" "SCEquipments" ON "SCCalls"."EquipmentID"="SCEquipments"."EquipmentID") INNER JOIN "Cofinal"."dbo"."ShAgents" "ShAgents" ON "SCCalls"."TechnicianID"="ShAgents"."AgentID") INNER JOIN "Cofinal"."dbo"."ARCustomers" "ARCustomers" ON "SCWorkOrders"."CustomerID"="ARCustomers"."CustomerID"
ORDER BY "ARCustomers"."CustomerName", "SCCalls"."AddressStreet", "SCEquipments"."EquipmentNumber", "SCCalls"."Date"
To o'nation:

Well, it looks like you used Access to write this so obviously it's ugly. The aliasing is inefficient. No real SQL programmer would write an ANSI sql like that. I could rewrite that same query in non-Ansi and it would be just as verbose.

A question for Tom:
what is the difference between the explicit where clause in your example -

SELECT col1, col2
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)
INNER JOIN T3 ON(T2.x = T3.x AND T2.y = T3.y)
INNER JOIN T4 ON(T3.x = T4.x)
WHERE col3 = 'foo' -- I assume this is T1.col3

versus

SELECT col1, col2 -- I hope you do anyway
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)
INNER JOIN T3 ON(T2.x = T3.x AND T2.y = T3.y)
INNER JOIN T4 ON(T3.x = T4.x)
AND T1.col3 = 'foo' -- I know this is valid and there's a
-- subtle difference I think.
The only problem I have with this query is the cursed double quotes around everything. Otherwise it seems pretty straightforward.
Add Comment



Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.