March 22, 2007 at 11:48 am
Hello everyone,
Is there any differences in these queries ?
SELECT p_id, c_id, s_id, r_id, first_name, last_name, full_name, country_id,
d_email, e_confirm, e_notify, start_page, r_type, offset FRO V_test
WHERE ((p_id = (SELECT app_by_id FROM weeks WITH(NOLOCK)
WHERE w_id='AD8AFEE111D249CE93A5A6869F4E088E')
OR p_id =
(SELECT cl_re_to_id from v_weeks WITH(NOLOCK)
WHERE w_id='AD8AFEE111D249CE93A5A6869F4E088E'
)))
AND (s_id=1) AND (e_confirm=1 AND ((p_id NOT IN
(SELECT p_id FROM pe_email_pre WITH(NOLOCK) WHERE obj_t_id = 4) OR p_id IN
(SELECT p_id FROM pe_email_pre WITH(NOLOCK) WHERE e_confirm = 1 AND obj_t_id = 4))))
SELECT p_id, c_id, s_id, r_id, first_name, last_name, full_name, country_id,
d_email, e_confirm, e_notify, start_page, r_type, offset FRO V_test
WHERE ((p_id IN (SELECT app_by_id FROM weeks WITH(NOLOCK)
WHERE w_id='AD8AFEE111D249CE93A5A6869F4E088E')
OR p_id IN
(SELECT cl_re_to_id from v_weeks WITH(NOLOCK)
WHERE w_id='AD8AFEE111D249CE93A5A6869F4E088E'
)))
AND (s_id=1) AND (e_confirm=1 AND ((p_id NOT IN
(SELECT p_id FROM pe_email_pre WITH(NOLOCK) WHERE obj_t_id = 4) OR p_id IN
(SELECT p_id FROM pe_email_pre WITH(NOLOCK) WHERE e_confirm = 1 AND obj_t_id = 4))))
The first query uses = operator in place of IN operator. The first query gives faster result than second one.
w_id is primary key of weeks table
V_test and V_weeks are two views.
Both query produces same result.
IS there any hidden difference in replacing IN operator with = operators in these queries ?
The second query leads more logical reads first one .
Please provide some suggestions ASAP.
Thanks
AKP
March 22, 2007 at 12:48 pm
>>IS there any hidden difference in replacing IN operator with = operators in these queries ?
Yes, there is a difference and it depends on the subquery:
SELECT cl_re_to_id from v_weeks WITH(NOLOCK)
WHERE w_id='AD8AFEE111D249CE93A5A6869F4E088E'
If this sub-query is guranteed to never return any more than 1 row, then you should not use IN (). If it can return more than 1 row, then you *must* use IN (), because use of '=' with a sub-query that retruns more than 1 row will actually give you errors at query execution time.
March 22, 2007 at 12:55 pm
Thats true but w_id is primary key, is there any possibilities of getting more than one row.
Thanks.
AKP
March 22, 2007 at 1:43 pm
>>is there any possibilities of getting more than one row
It depends. w_id is the primary key of a table.
Your sub-query selects from a view. Since we don't know what your view definition is, or if it contains joins or union operators, there is no way of knowing what it will return.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply