Is there a point to not using joins?
Example:
Select *
from tbl_a a, tbl_b b
where a.uid = b.uidversus
select *
from tbl_a a inner join tbl_b b on a.uid = b.uid
When I had switched a lot of queries to use joins, I noticed that there was a significant boost in how the query is handled.
But does it come at a cost?JOIN syntax comes at a cost? no :)|||where syntax is supported for legacy reasons, join is the more recent ISO/ANSI standard SQL.
I doubt where will be dropped as a mechanism of associating tables but it pays to be with the current standard rather than the old standard if all you SQL will run on the newer standard|||You were asking the wrong question: Both versions you posted are joins. The first is a so-called implicit join and the latter an explicit join. Both are standardized in ISO/IEC 9075:2003, i.e. the most recent SQL standard.|||where syntax is supported for legacy reasons, join is the more recent ISO/ANSI standard SQL.
I doubt where will be dropped as a mechanism of associating tables but it pays to be with the current standard rather than the old standard if all you SQL will run on the newer standard
It's not a question of being the newer standard or all that. To be relationally complete, you have to support a cartesian product. If you support that, you implicitly support the "old" syntax.
Look at it this way: so you were running and old machine and you had to multiply two integers like so:
unsigned multiply(unsigned a, unsigned b) {
unsigned c = 0;
while(a--) {
c = c + b;
}
return c;
}
Now, on the old 6502 chips (remember the C-64?) there was no multiply instruction, so rolling your own isn't all that farfetched. (Of course, you wouldn't do it in C, but this is supposed to be illustrative...)
But just because you now have a built in multiply instruction doesn't mean you're going to ditch addition. It's part of arithmetic, you have to have it.
In the same way, the "cartesian product and filter" way of doing joins is dated, but any proper DBMS is going to support it because it's part of the algebra.|||It's not a question of being the newer standard or all that. To be relationally complete, you have to support a cartesian product.
Also for a (full) cartesian product, one can either use the "old" (implicit) join syntax or the explicit SELECT *
FROM t1 CROSS JOIN t1
So, if you like to, you can systematically use the explicit JOIN syntax everywhere, and forget about commas in FROM.
(As a matter of fact, I always do: it's far better for readability and maintainability.)|||I see no reason for constructing SQL joins using the old and now out dated syntax of specifying the list of sets to be joined in the from clause separated by commas, and including the join condition in the where clause of the query. The problem with this approach is that it is hard to read, it causes confusion and most importantly, it does not properly convey the intent of the query. Yes, it can be done without JOIN syntax, but readers of the query must then search through the many lines of conditional syntax to determine which expression relates to a single set and which relates to multiple sets, simulating the behaviour of the join operator.
I remember working for a firm where the head of development would caution developers who failed to adhere to correct syntax and development standards. His view, shared by many senior members, was that if you tried to be a "clever dick" and write code that was unnecessarily difficult to read then you were not suitable to work as a developer, who has a responsibility to develop code that is of a level of complexity proportional to the complexity of the problem, and which can be understood in a reasonable period of time by a person of similar ability.|||I wouldn't call the implicit join syntax "outdated" (it is in the ISO SQL standard, after all), but I fully agree with you on the coding standards.
Usually, it takes longer to think about how to resolve a problem than to write it down/type the code in. Thus, everyone using short and cryptic variable names, for example, is just such a "clever dick" for me.
No comments:
Post a Comment