WHERE EXISTS - 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 2005 Forums

Transact-SQL (2005)

WHERE EXISTS

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-11-03 : 05:57:00

USE pubs
SELECT city,state,country,pub_name,pub_id
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')

Can someone please explain me how will the above query get executed? Will the subquery be evaluated first or the outer query?

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 06:05:44

fisrt it takes the id from outer query and passes it to sub query to evaluate it. then it applies EXISTS operator to see if there are any results returned. Based on it, it will return value from main query for those id's which returned a valid resultset from subquery.

rn5a
Starting Member

25 Posts

Posted - 2008-11-03 : 06:24:17

quote:
Originally posted by visakh16

fisrt it takes the id from outer query and passes it to sub query to evaluate it. then it applies EXISTS operator to see if there are any results returned. Based on it, it will return value from main query for those id's which returned a valid resultset from subquery.

Do you mean to say that the outer query will get evaluated first & then the subquery (in the EXISTS clause)? If that is the case, aren't subqueries always evaluated before outer queries provided the subquery is not a correlated subquery?

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 07:07:20

http://www.databasejournal.com/features/mssql/article.php/3485291

rn5a
Starting Member

25 Posts

Posted - 2008-11-04 : 02:08:39

quote:
Originally posted by visakh16

http://www.databasejournal.com/features/mssql/article.php/3485291

CREATE TABLE [StoreInfo] (
[StoreName] [varchar] (50) NULL ,
[Sales] [int] NULL
) ON [PRIMARY]

---------------------

INSERT INTO StoreInfo VALUES ('Los Angeles', 1500)
INSERT INTO StoreInfo VALUES ('San Diego', 250)
INSERT INTO StoreInfo VALUES ('Los Angeles', 300)
INSERT INTO StoreInfo VALUES ('Boston', 700)

---------------------

CREATE TABLE [Geography] (
[Region] [varchar] (50) NULL ,
[StoreName] [varchar] (50) NULL
) ON [PRIMARY]

---------------------

INSERT INTO Geography VALUES ('East', 'Boston')
INSERT INTO Geography VALUES ('East', 'New York')
INSERT INTO Geography VALUES ('West', 'Los Angeles')
INSERT INTO Geography VALUES ('West', 'San Diego')

---------------------

When I run the following query:

SELECT SUM(Sales) FROM StoreInfo
WHERE EXISTS
(SELECT * FROM Geography
WHERE Region = 'West')

the output is 2750 but shouldn't the output be 2050 since in the Geography table, only Los Angeles & San Diego are in the West. Why is the 700 under the Sales column in the StoreInfo table (corresponding to Boston which is in the East as per the Geography table) getting added?

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 02:37:33

your above query doesnt make much sense. The subquery does not have any relation with main query as you're not linking it with main query by means of any field. As of now, it returns the sum of all the Sales fields of yourtable when there's at least one record existing on Geography for Region West.I think you should modify it like below

SELECT SUM(s.Sales) FROM StoreInfo s
WHERE EXISTS
(SELECT * FROM Geography
WHERE Region = 'West'
AND SomeColumn=s.SomeColumn)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-04 : 02:40:40

Your subquery has no relation to the outer query.


SELECT SUM(si.Sales) FROM StoreInfo si
WHERE EXISTS
(SELECT * FROM Geography g
WHERE g.Region = 'West'
  and g.StoreName = si.StoreName)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-04 : 02:43:00

again - too late...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources