Sub-select vs. Join - SQL Server Forums

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)

Sub-select vs. Join

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-14 : 10:40:44

Steve writes "I'm developing a stored proc that produces a query result
set.

Let's say that it is a query to select all addresses that
are in a collection of States.

Here is the State Code Table:

StateCode State

01 Arizona
02 Alaska
03 Alabama
04 California
05 Texas
06
.
.
.
50 Nevada

I will build a table that holds a user's selection of
states -- for example:

tUserSelections

SelectionID int
SelectionGroup int
StateCodeID int

Data for that will look like

SelectionID      Selection Group    StateCode

0001                 123                01
0002                 123                05
.
.
.
.
1234                 888                02
1235                 888                03
1236                 888                50

The query will select information from the Address table

AddressID INT
Address Varchar(30)
City Varchar(30)
StateCode Int
Zip Char(5)

(from information collected in tUserSelections)

I am considering 2 design options:

Sub-select in Where Clause:

CREATE PROCEDURE Option1

( @SelectionGroup int

)
As

SELECT Address, City, Zip
FROM dbo.tAddress
WHERE (StateCode IN(SELECT StateCode FROM
tUserSelections where SelectionGroup = @SelectionGroup))

Or Join:

CREATE PROCEDURE Option2

( @SelectionGroup int

)
As

SELECT Address, City, Zip
FROM dbo.tAddress INNER JOIN tUserSelections ON
dbo.tAddress.StateCode = UserSelections.StateCodeID
Where tUserSelections.SelectionGroup = @SelectionGroup

(Performance could probably be further optimized by
embedding this join into a view)

Anyone been through this exercize before .... Is one
approach clearly better ???

Metrics are probably important ..... there will be many
more records in the Address table than in the
UserSelections table

Any tips on how to architect the indexing and definition
of relationships between the tables will be appreciated.

Thanks,

Steve"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-14 : 11:40:19

Steve,

I prefer to do JOINs when I can, but this would be a good time for you to read up on execution plans in Books Online or another good resource. By comparing execution plans between the two proposed statements, you'll get a feel for which would be more efficient (assuming your test data is somewhat representative of the production environment).

General guidelines for indexes: Index fields used in WHERE clauses and JOINs.

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources