Re: SubQuerying Vs Joining
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 10/30/03
- Next message: supernova: "more service pack question"
- Previous message: Awah Teh: "SubQuerying Vs Joining"
- In reply to: Awah Teh: "SubQuerying Vs Joining"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 29 Oct 2003 19:01:48 -0500
Awah,
First off the one that is most likely to be fastest is not shown. This
would be an EXISTS statement.
Select * from users AS b where EXISTS (select * from
users_site_passes AS a where a.User_id = b.User_ID AND a.date_pass_issued
>= 'January 1, 2003')
Exists will stop looking after the first match is found where as the other
two might do more work. To answer your original question though they may in
fact be the same. Sometimes the optimizer will create similar plans on
queries such as these. Here are some general comments though:
Get in the habit of using the ANSI JOIN syntax like this:
Select users.* from users INNER JOIN users_site_passes
ON users.user_id = users_site_passes.user_id and
users_site_passes.date_pass_issued
When dealing with DATE strings you should use the ANSI syntax as well to
avoid issues where sql server can misinterpret the date. It goes like
this: 'yyyymmdd' Always has 8 chars and no dashes, slashes etc.
-- Andrew J. Kelly SQL Server MVP "Awah Teh" <awaht@digicentriq.com> wrote in message news:%238AEccnnDHA.644@TK2MSFTNGP11.phx.gbl... > Which is most effective (consider the query below for an example)? > Please give me some explanation (FMI:) as to why one is faster over the > other. > > > --Both Queries are designed to get the users information of users that > received passes in the Year of 2003 > > --** *************** > --** QUERY 1 > --** *************** > Select * from users where user_id in (select user_id from > users_site_passes where date_pass_issued >= 'January 1, 2003') > > > --** *************** > --** QUERY 2 > --** *************** > Select users.* from users, users_site_passes where users.user_id = > users_site_passes.user_id and users_site_passes.date_pass_issued >= 'January > 1, 2003' > > Thanks In Advance > A- > > -- > Awah Teh > Chief Executive Officer > DigicentriQ Technologies, LLC > awaht@digicentriq.com > www.digicentriq.com > 877 675 4742 > 805 732 9421 > >
- Next message: supernova: "more service pack question"
- Previous message: Awah Teh: "SubQuerying Vs Joining"
- In reply to: Awah Teh: "SubQuerying Vs Joining"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
