I prefer to list out your tables and then do your joins in the "where" clause. For 3+ tables and mixing inner & outer joins it's especially better. I think maybe this only looks better once you become really really good at reading and writing SQL though. Maybe it's also because I come from the Oracle world where it is the standard.
1. I hate needing to parse/use the '(+)' to denote which way an outer join goes. It's much easier to read "left outer join".
2. Separation of concerns. This way all of the conditions that are in the WHERE clause act as filters on the data, and all of the JOIN conditions sit right next the the relevant tables in the FROM clause.
3. I don't even know what the WHERE clause syntax for a CROSS JOIN would be.
4. When a single JOIN has multiple conditions, it's much nicer to have them all together in the FROM clause than to have them scattered (or even grouped) in the WHERE clause.
5. You can use USING. When you're joining two tables on a column with the same name (e.g. product_id), I always find it annoying to be required to specify which table's column to look at since they are guaranteed to be equal.
I don't consider myself a 'database expert', but I spend several years in an environment where 100+ line queries with 5-10 joined tables was not uncommon (so I'm not coming from a place of only having worked with simplistic queries).
(+) syntax is awesome =)
I probably should start using the join syntax. Old habits die hard.
[...]
> Maybe it's also because I come from the Oracle world where it is the standard.
IME, lots of Oracle experts -- who are likely to be longtime heavy users that started when Oracle didn't support explicit joins -- often prefer implicit joins because its what they are used to.
But its much clearer when you need to reuse and modify a query -- particularly someone else's query -- to have the join conditions clearly identified and distinct from filtering conditions.
I really hope you are not suggesting going back to things like <star>= and =<star>
I started out writing joins in the where clause; i'm not sure why, probably because that's how it was done in the first book i read. I then worked with some old Oracle hands who also did it that way, probably because of tradition. A year or two ago, i rediscovered the explicit join notation, and switched over to it completely in a matter of days.
As you say, explicit joins are right because they separate which tables/which relationships and which rows. Those are not particularly distinct things in the pure relational algebra model, but they are fully distinct in practical use.
It is not "semantically wrong": conceptually, you can compute a join by taking the Cartesian product and then applying the join predicates to the result, which is actually what the implicit join syntax suggests. Semantically, there is no difference between applying an (inner) join predicate and applying any other predicate. There is also no actual division between "building the set" and "filtering the result": different predicates might be applied in different orders, before or after different relations in the query have been accessed.
select a.foo, b.bar, c.blah
from blahblah a
join whatever b on a.ID = b.A_ID
join another_table c on b.ID = c.B_ID
where 1=1
and a.foo = "baz"
if you have a very simple join it really makes no difference. On the other hand, when you have 10 tables involved in a 200 line query, debugging and maintaining the query is directly proportional to how well you can structure it. Breaking the query down into functional, related pieces which can be reviewed and managed separately is the key to maintaining large queries.
The way I describe it is this: In C or Perl, the complexity of debugging is linearly proportional to the length of the function. State accumulates during the function and consequently you have to read each line in context with those which came before and after.
With well-structured SQL, OTOH, you have something different. You have a set of sub-functions which are each somewhat independent and state does not (usually, outside of specified window functions) accumulate. Because it is declarative, a well structured query can be debugged not as a linked list of statements but as a tree. This is far more efficient. Personally I find a well-structured 200 line SQL query to be far more maintainable than a 50 line Perl function. And if it is really well structured, you could probably push this further out.
The pragmatist in me remembers too many painful times fighting with overcomplicated "WHERE" clauses and the bizarre (+) syntax in old Oracle servers.
Advantage: pragmatist.
There may be some magical hypothetical relational-theory language where this sort of approach can be cleanly expressed, but Oracle PL/SQL isn't it.
Also, I believe that in SQL Server, the old (ANSI 89) style of joining (joining in the where clause) was deprecated and isn't optimized anymore so you'll get crap performance because all of the tables are actually cross joined before the filtering happens. The explicit join forces relationships to be accounted for first which can help keep the amount of memory needed down.
One last thing, how long does it take to adapt a new standard? The explicit join has been the standard since 1992.
And I'm 100% with the OP, not you, sorry. Explicit joins are, IMHO, enormously clearer and easier to work with, particularly with outer joins, cross joins (the rare times they're intentional), CROSS APPLY....
Each to their own though. You write the code that's easier for you and your team to read; I just think it's unlikely we'll be happy working on a team together ;-)