December 22, 2008 at 7:38 am
I was curious if a select statement could be run to avoid a join. For example:
use ScratchDB
create table TableA
(
tblID int,
tblValue varchar(10)
)
create table TableB
(
tblID int,
tblValue varchar(10)
)
insert into TableA values(1, 'Row 1')
insert into TableA values(2, 'Row 2')
insert into TableA values(3, 'Row 3')
insert into TableA values(4, 'Row 4')
insert into TableA values(5, 'Row 5')
insert into TableB values(1, 'Row A')
insert into TableB values(2, 'Row B')
insert into TableB values(6, 'Row F')
select a.tblValue [Rows for Table A], b.tblValue [Rows for Table B]
from TableA a, TableB b
where a.tblID = b.tblID
drop table TableA
drop table TableB
In the above code, the select works just as if I wrote it with
select a.tblValue [Rows for Table A], b.tblValue [Rows for Table B]
from TableA a -- edited to fix syntax
inner join TableB b on (a.tblID = b.tblID)
Is this a quirk/coincidence and should it be avoided as a general rule if you have two tables with the same type of identity, going instead with the inner join?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
December 22, 2008 at 8:00 am
What you're describing is old style join syntax. It is the same thing as a join, but it's not seen much anymore because JOIN's are much easier to read. (Your second query should have the , TableB b removed to be equivalent)
There is no functional difference between the old method and the new method, they will be parsed in an identical fashion, it's just a difference in readability.
December 22, 2008 at 8:03 am
You should use the new join syntax. The old syntax is only really being supported for backwards compatibility and the *= syntax for outer joins is already unsupported.
As far as performance - there is really no difference. If you can write it in the old syntax, you can write it in the new syntax in a way that will give you the exact same execution plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply