I love using small CTE's to help with the hassle of the alternative - creating temp tables and then cleaning them up. They can be particularly useful for data modification, as you can't ordinarily do a recursive delete or insert all that easily.
PS: I always cringe when I see this sort of syntax though:
"FROM
users,
tasks,
project"
Much clearer using explicit JOINS imo, especially if your goal is readability.
Not just cleaner (which might be debatable), but having a separate ON clause allows you put criteria for selecting rows after the join has occurred in the WHERE clause, which are different than criteria for making the join in the first place. Or so I prefer, but whatever.
Completely disagree. All the database developer "experts" I know of (including myself) hate the explicit join syntax.
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.
I've worked in a mixed Oracle / PostgreSQL environment, and I prefer the explicit join syntax.
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).
I'm the opposite. I hate joins in the where clause because it is semantically wrong and confuses the building the set part with the filtering the result part. I want to see how a table is joined into the query at the place it is joined and additional filtering in the where. To me it makes it much easier to follow and not miss a join item, especially if the join happens on multiple columns.
I really hope you are not suggesting going back to things like <star>= and =<star>
I hate joins in the where clause because it is semantically wrong and confuses the building the set part with the filtering the result part.
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.
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.
> Completely disagree. All the database developer "experts" I know of (including myself) hate the explicit join syntax.
[...]
> 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 went from from a company that generally adopted the implicit syntax to one that uses explicit, and after some adjustment, I've come to slightly prefer the latter. One thing that helps is to indent the join condition, formatting the SQL as:
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"
This becomes less clear with certain types of joins, but generally, I've come to prefer it.
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.
I 100% disagree with you. How is having even more conditions in the where clause helpful? It makes it very easy to accidentally cross join (Oops, I forgot to actually join that table because I have 40 lines in my where clause).
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.
I can't claim to have worked with a representative sample of SQL developers, but I've worked with a lot and written huge quantities myself, and I'd like to think some of it was near the 'expert' level.
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 ;-)
I think you're either very selective with your sources or very selective about who you give the "expert" title to. I don't know that I've seen implicit joins used by a speaker at a SQL event in the past couple years, unless it's for cartesian joins.
My experience is the exact opposite. Only absolutely beginner php/mysql guys do that. And they do so because they don't understand joins. You can't even do outer joins implicitly without vendor specific extensions (which are being deprecated), so the idea that it is better when mixing join types is particularly odd. It is also far from "the standard" in the oracle world. Oracle land has more people who think using weird oraclisms are good, but there's still an awful lot of people who prefer ANSI syntax there. Just look at the answers to questions about it on any forum.
PS: I always cringe when I see this sort of syntax though: "FROM users, tasks, project"
Much clearer using explicit JOINS imo, especially if your goal is readability.