Am I a dinosaur?

[TJRTech]

That's the question...please be nice in your answers.

A little background might be helpful. I find that everyone around me and most on this forum use the (LEFT) JOIN syntax for constructing queries with joins.

I, on the other hand, like to place all the tables in the FROM clause, connect them up in a logical order in the first few statements in the WHERE clause, using *= for LOJ, and then place any additional criteria last in the WHERE clause.

I find it easier to read because it "flows"...I see the tables, I see how they are connection (and I use a transitive flow for that...A to BC to BC to C, etc as much as possible.

Am I a dinosaur? Is this harder or easier to read?

I understand Oracle has problems with this approach, is that correct?

Comments?

TR

 

[Golom]

LEFT and/or RIGHT and the "ON" condition are ANSI-standard constructs. The "*=" and "=*" notation in the Where clause are still supported but are considered outmoded in the brave new world of SQL-92. My suspicion (and I can't prove it) is that they will continue to be supported because their elimination could break a lot of legacy code (like yours.)

As to easier to read ... purely an "eye of the beholder" judgement I think.

 

[SQLSister]

This is a very dangerous practice in SQL Server.

From Books online:
"In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way."

Note that using the old syntax for inner joins is fine, but for left and right joins you may not get the results you woant. In fact, I've seen cases where it gives a cross join which is very bad.

 

[TJRTech]

Thanks, Sis!

I have NEVER had an ambiquity or strange results and have been using the *= sequence for SQLServer systems since 1995 (has it really been 9 years).

But then again, I try to design things such that left outter joins are avoided as much as possible...maybe doing only a couple for an entire application/website.

I do know the new syntax, but it just doesn't roll off my fingertips, yet. Guess I will sharpen up.

Thanks for the comments.

TR

 

[nigelrivett]

I took a long time to switch over too but now stick to ansi syntax.

The abbreviated syntax is restrictive when it comes to outer joins as they can't be nested whereas the ansi syntax allows it.
It also causes problems with views - if a view is created with the abbreviated syntax it limits the queries you can use.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Also I now find it easier to read as the table joins are all separated.
It does require you to be consistent about the formatting though and to be consistent about what goes in the where clause and what in the join. Makes it very easy to copy from one query to another.

be careful though
select *
from t1, t2
where t1.a *= t2.a
and t2.b = 1

is the same as
select *
from t1
left join t2
on t2.a = t1.a
and t2.b = 1

not
select *
from t1
left join t2
on t2.a = t1.a
where t2.b = 1


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.