A question about selects.

Question

A question about selects.

Mark Kinnear

SSC Eights!

Points: 850
More actions
June 26, 2008 at 2:04 am

#94299

Just out of interest sake I noticed that some SQL nerds such as myself have different ways of using select statments between 2 table that have a relationship.
I prefer a join it looks neater and performs just fine, yet others (guys who have been in this business a lot longer than me, i observed) use a select/where.
I have done some comparisons with no massive difference in time between the 2.
Is this a preference thing? and which is better coding practice?
--Join
Select *
From #Temp T
Join #Temp2 T2 on T2.RelationshipID = T.RelationshipID
--vs
--where
Select *
From #Temp T, #Temp2 T2
Where T2.RelationshipID = T.RelationshipID
Regards.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

paulfelce-755299 SSC Journeyman Points: 98 More actions · Answer 1

An excellent question.

I'm guessing that the join syntax is preferred as you can use it to easily express the intention of left and right joins too, which I don't know how to do with the 'Where' syntax?

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 2

I know what you mean..

At uni I learnt to use the WHERE type.. So my queries always 'used' looked like

SELECT *

FROM classes c, students s

WHERE c.cl_studentID=s.st_studentID

and cl_studentID = 4

But this is only good when your using INNER JOINS

Using JOINs definately looks better as it allows you to see exactly what joins you have, left, right, outer, inner etc.

If you are going to be joining tables, you might as well do it the same way each time. That's why I changed.. oh and the fact my boss gave me in trouble! haha

SQL SERVER Central Forum Etiquette[/url]

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You can use *= and =* for outer joins when putting joins in the WHERE clause. This is the old ANSI standard for join syntax. Using the JOIN keyword in the new syntax.

Don't use the old syntax anymore.

Damian-1673 SSCommitted Points: 1622 More actions · Answer 4

for me I have been using “Where” clause in my sql scripts and I find it very easy to learn and if you have a very big script that goes in to many number of pages I find it much better to understand + it will be useful for me to re design the same queries to another database platforms like mysql or oracle.

Steven Cameron SSCrazy Points: 2903 More actions · Answer 5

I think the JOIN makes it easier for others to understand the code and how the tables are joined. If you have a number of tables joined using a WHERE, you would have to search through the WHERE to find how they are joined.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

SQL 2008 is the last version the old syntax will still be available.

http://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Damian-1673 SSCommitted Points: 1622 More actions · Answer 7

this means after sql 2008 I have to re write all the old scripts if I have Where Clause?

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 8

Damian (6/26/2008)
this means after sql 2008 I have to re write all the old scripts if I have Where Clause?

Only if you're using the *= =* type syntax. Which has been deprocated since about 1483 anyway

GSquared SSC Guru Points: 260824 More actions · Answer 9

There can be differences in performance and results between the two ways of writing the query. In a more complex query, having the join data in the From clause can improve performance. In the case of outer joins, having them in the Where clause can actually "break" the query (turning it into an inner join even if you really want an outer join).

Having this stuff in the From clause doesn't have any negative consequences that I'm aware of. Having it in Where can have negative consequences sometimes. So I always put my join data in my From clause.

Makes it easier to read, too.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

Michael Earl (6/26/2008)
You can use *= and =* for outer joins when putting joins in the WHERE clause. This is the old ANSI standard for join syntax. Using the JOIN keyword in the new syntax.
Don't use the old syntax anymore.

Don't use that at all... first, it doesn't always work as you'd expect it to... second, I don't believe it's even available in SQL Server 2005.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Jeff Moden (6/26/2008)
Michael Earl (6/26/2008)
You can use *= and =* for outer joins when putting joins in the WHERE clause. This is the old ANSI standard for join syntax. Using the JOIN keyword in the new syntax.
Don't use the old syntax anymore.
Like Michael said, I recommend that you don't use that at all... first, it doesn't always work as you'd expect it to... second, I don't believe it's even available in SQL Server 2005.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)