May 26, 2011 at 2:59 am
[font="Verdana"]Please if following queries, with their respective output, can be explained in simple?[/font]
create table employee(
IDint,
namenvarchar (10),
salaryint,
start_date datetime,
citynvarchar (10),
regionchar (1)
)
insert into employee (ID, name, salary, start_date, city, region)
SELECT 1, 'Jason', 40420, '02/01/94', 'New York', 'W'
Union
SELECT 2, 'Robert',14420, '01/02/95', 'Vancouver','N'
Union
SELECT 3, 'Celia', 24020, '12/03/96', 'Toronto', 'W'
Union
SELECT 4, 'Linda', 40620, '11/04/97', 'New York', 'N'
Union
SELECT 5, 'David', 80026, '10/05/98', 'Vancouver','W'
Union
SELECT 6, 'James', 70060, '09/06/99', 'Toronto', 'N'
Union
SELECT 7, 'Alison',90620, '08/07/00', 'New York', 'W'
Union
SELECT 8, 'Chris', 26020, '07/08/01', 'Vancouver','N'
Union
SELECT 9, 'Ary', 60020, '06/09/02', 'Toronto', 'W'
GO
SELECT *
FROM Employee
WHERE Salary > 500 AND Salary < 1000
AND ID = 1 OR ID = 2
GO
---------------- OUTPUT ------------------------
--IDnamesalarystart_datecityregion
--2Robert144201995-01-02 00:00:00.000VancouverN
------------------------------------------------
SELECT *
FROM Employee
WHERE ID = 1 OR ID = 2
AND Salary > 500 AND Salary < 1000
GO
---------------- OUTPUT ------------------------
--IDnamesalarystart_datecityregion
--1Jason404201994-02-01 00:00:00.000New YorkW
------------------------------------------------
SELECT *
FROM Employee
WHERE Salary > 500 AND Salary < 1000
---------------- OUTPUT ------------------------
--IDnamesalarystart_datecityregion
------------------------------------------------
[font="Verdana"]
Thanks [/font]
May 26, 2011 at 5:28 am
Yeah, it's just logical processing. You can see that none of the salaries fall within the >500 < 1000 range, so how did the first two queries return data? Because you don't have any parenthesis. The whole set of WHERE clauses is being evaluated all at once and the 'ID = 1 OR' and 'OR ID = 2' result in rows exclusive of the rest of the paramters. If you change the query to this, you'll get consistent results:
SELECT *
FROM Employee
WHERE (Salary > 500 AND Salary < 1000)
AND (ID = 1 OR ID = 2)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2011 at 5:37 am
Grant Fritchey (5/26/2011)
Yeah, it's just logical processing. You can see that none of the salaries fall within the >500 < 1000 range, so how did the first two queries return data? Because you don't have any parenthesis. The whole set of WHERE clauses is being evaluated all at once and the 'ID = 1 OR' and 'OR ID = 2' result in rows exclusive of the rest of the paramters. If you change the query to this, you'll get consistent results:
SELECT *FROM Employee
WHERE (Salary > 500 AND Salary < 1000)
AND (ID = 1 OR ID = 2)
[font="Verdana"]Thanks Fritchey,
But as you said "The whole set of WHERE clauses is being evaluated all at once", please can you explain predicates evaluation precedence in case 1 and 2!
As in first case it output row 2 but in second case it output row number 1. :ermm: which is quite confusing !
Waiting ... ![/font]
May 26, 2011 at 5:39 am
AND takes precedence over OR, it's done first. So without brackets this:
ID = 1 OR ID = 2 AND Salary > 500 AND Salary < 1000
is actually evaluated like this
ID = 1 OR (ID = 2 AND Salary > 500 AND Salary < 1000)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2011 at 5:57 am
Abrar Ahmad_ (5/26/2011)
Waiting ... ![/font]
I'm sorry, normally I let this stuff go, but, seriously, Waiting? I'm in here helping out because I want to be, not because you're paying me. Please try to keep that in mind when responding to everyone here. We're all volunteers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2011 at 9:36 pm
Grant Fritchey (5/26/2011)
Abrar Ahmad_ (5/26/2011)
Waiting ... ![/font]
I'm sorry, normally I let this stuff go, but, seriously, Waiting? I'm in here helping out because I want to be, not because you're paying me. Please try to keep that in mind when responding to everyone here. We're all volunteers.
[font="Verdana"]
Shortcuts cause such ambiguity, otherwise my request was not such as perceived! Sorry if you got hurt.
Thank you again!
[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply