This content has been marked as final. Show 25 replies
I would have preferred it if the nonsensical LEFT/RIGHT keywords were optional and you could just write
FROM strongside OUTER JOIN weakside
which I think would be a lot clearer. I can't imagine how theey dreamt up the word "OUTER" either, for that matter. Some relational theorist chooses a random word and we all have to make what sense we can of it. Surely the distinction is not between "inner" and "outer" anything, but between weak and strong sides of the join.
not to forget CROSS JOIN, which is no join (carthesian product) most of the time
but I have already used CROSS JOIN once or twice here, like in
select * from a,b is the same as select * from a cross join b
which is a pain to write with (+)
select * from a full join (b cross join c);
>devmiral thats not quite right, as by seperating with , you include the table again.
FROM a LEFT OUTER JOIN b ON a.colx = b.colx,
a LEFT OUTER JOIN c ON a.coly = c.coly,
b LEFT OUTER JOIN d ON b.colz = d.colz
I think (no db access to test) this is correct:
LEFT OUTER JOIN b ON a.colx = b.colx
LEFT OUTER JOIN c ON a.coly = c.coly
LEFT OUTER JOIN d ON b.colz = d.colz
Agreed LEFT and RIGHT are rubbish!
Or you could write the join conditions forwards. Or Jonathan Lewis and I could give up banging on about it since everyone seems to prefer backwards for reasons I will never understand (much like the enduring popularity of BINARY_INTEGER in PL/SQL, just to wander completely off topic - anyone else noticed that?)
"anyone else noticed that?" What, you wandering completely off topic?
Which way did he go left or right?
I am much happier knowing all you need is left and that right is only for twisted psychos. Unfortunately now I have to get used to forward and backward as well. No doubt the eventual incorporation of forward and backwards into the SQL syntax by ANSI will help clarify that too. It seems that we only really need forward joins, but that people are mostly backward psychos in this respect.
but that people are mostly backward psychos in this respect.!taht tneser I ,yeH
> Which way did he go left or right?
He made three lefts ;)
The main reason I absolutely prefer ansi joins to Oracle joins is that you separate the join conditions from the business logic conditions.
from table1, table2
where table2.c1=table1.c1 -- join condition
and table2.c2=table1.c2 -- join condition
and table1.c3='abc' -- business rule condition
and table1.c4 is not null -- business rule condition
join table2 on table2.c1 = table1.c1
and table2.c2 = table1.c2
and table1.c4 is not null
The example above is very simple, but imagine a join with 10 tables; the where clause can get huge and hard to identify what conditions are joining tables and which ones are based on business rules. The ansi syntax makes this separation much clear I think.
I agree on that, to read SQL queries with explicit JOIN syntax, not only for OUTER JOINS but also for INNER JOINS is much better to read, and also more easy to write. The only reason to use the old syntax is when you are too lazy to learn something new. I my company we do not allow implicit SQL anymore.