Difference between table1, table2 and table1 inner join table2
-
Hi,
What's the difference between using Table1, Table2 in FROM or using a join between both tables?
Example:
SELECT table1.id, table1.name, table2.amount
FROM table1, table2
where table1.id = 25 and table1.id = table2.id
and
SELECT table1.id, table1.name, table2.amount
FROM table1 inner join table2 on table1.id = table2.id
WHERE table1.id = 25
Kindest Regards,
@puy Inc
-
I suggest you use first syntax if you're british, and second, if you're american. If you're neither, simply switch from time to time
possible solutions:
a) use second syntax, forget the rest und you're well off.
b) dig into the articles about ansi-standards, implications of join logic vs. result set filtering etc., spend some weeks on the subject and you'll use second syntax.
_/_/_/ paramind _/_/_/ -
I have always preferred using joins, but a couple hours ago I saw a query written by a co-worker not using joins.
The execution plan was the same, so I couldn't convince him to use joins...
I did look into this forum to find out another conversation about this topic but didn't find any.
I will do a little of research, thanks.
Kindest Regards,
@puy Inc
-
The first is the old method and the second is the newer standard. Ultimately the engine will translte the first into the second. Also the second is more readable than the first as you don't have to look for the conditions that bring the two together (especially in long queries).
-
IMO the JOIN-syntax using the on clause differantiates better wat's a join-predicate (on-clause) and what's a filter-predicate (where clause).
This makes is more ease for another person to understand your queries.
Check this forum. There are other threads regarding this subject.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
True, but the old standard will be discontinued.
>Ultimately the engine will translte the first into the second.
If the meaning is clear, yes. But this is not always the case.
Ok, way on: under "most" circumstances, the results are equal because the meaning is equal.
There are much better examples in the articles section here, but for a beginning consider this:
select
a
from t1
left outer join t2
on t1.id = t2.id
and t2.date < 'somedate'
------------------------------------------
select
a
from t1
left outer join t2
on t1.id = t2.id
where
t2.date < 'somedate'
These are not the same! Let alone the NULL values for all the missing t2.records.
With the old 'where'-syntax, it is often not clear, whether a join takes place and is afterwards filtered by the rest of the where-terms.
_/_/_/ paramind _/_/_/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply