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.
| Author |
Topic |
|
rmcclure
Starting Member
1 Post |
 Posted - 2008-02-28 : 11:10:54
|
| Hi,I have a simple select statement that joins a master and a detail tables using a single field. Looks somthing like this:Master:Field1 (Unique key)more fields...DateField (Index field)Detail:Field1 (unique key)Field2 (unique key)more fields....The master has 100 thousand records and the detail has 100 million records.If I had a statment "Select.....From Master, Detail" what would be the best way to write the where clause?Would one of the following where clauses run faster than the other based on the number of records in the tables?Where1:where Master.DateField = value and Master.Field1 = Detail.Field1Where2:where Master.Field1 = Detail.Field1 and Master.DateField = value |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-28 : 11:16:24
|
Join condition should be part of JOIN clause and additional filter condition should be part of WHERE clause.From Master JOIN Detail on Master.Field1 = Detail.Field1Where Master.DateField = value Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-28 : 11:16:30
|
| Try and avoid using joins in yor WHERE clause. Instead stick to using JOINS in your FROM clause. Use the WEHERE clause to filter the joined data. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-28 : 11:24:26
|
As a sidenote, sometimes if you have another WHERE clause over Detail table, you can write THAT clause in the JOIN.SELECT *FROM Master AS mINNER JOIN Detail AS d ON d.mID = m.ID AND d.Col9 = 34WHERE m.Col1 = 't'But this is not always better. It depends mostly on indexing and the granularity of detail and the additional clause.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Question about where clause in a select