Differnce between joins and comparing tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Differnce between joins and comparing tables

Author

Topic

Jenny
Starting Member

2 Posts

Posted - 2002-08-02 : 07:15:42

Hi!
I have "inherited" a database from a coworker. I discovered that the sql statements in the stored procedures are written in a different way than I would use.

Here is an example:

SELECT person.pnr, person.namn, person.adress2, person.postnr, person.Postort
FROM person, aktgald
WHERE person.personid = aktgald.personid AND aktgald.aktid = 2910

I would use:
SELECT person.pnr, person.namn, person.adress2, person.postnr, person.Postort
FROM person INNER JOIN aktgald ON person.personid = aktgald.personid
WHERE aktgald.aktid = 2910

What is the difference?
Thanks
Jenny

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-02 : 07:32:21

The latter form is ANSI standard....but the former works (or delivers the same result) in 99% of cases...I believe that there are 'some' situations where the latter can deliver different results and/or different performance....

search here for references to ANSI....and some previous discussions (and more lucid contributions) should appear....

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-02 : 07:35:48

Note: the former (non-ansi standard) will not be supported in the next version of SQL Server....

Jay White
{0}

admin001
Posting Yak Master

166 Posts

Posted - 2002-08-02 : 07:45:18

Hi,

The latter form returns all the columns in both tables i.e person and aktgald and returns only the rows for which there is an equal value in the join column. The result set will display that a column called persondid is common in both the tables pertaining to your WHERE clause.

Admin001

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-02 : 09:08:19

They mean the same thing.
They are both ANSI (although SQL-92 refers to the first form as 'old-style').
They will both work in the next version of SQL Server -- this is INNER/CROSS not OUTER joins we're talking about here!
They will generate the same query plan.*

The difference is that the INNER JOIN keeps the join conditions visually nearer the tables joined, so it's usually easier to see what's going on.

*probably

Edited by - Arnold Fribble on 08/02/2002 09:11:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources