Find the correct answer in the following query

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 2008 Forums

Transact-SQL (2008)

Find the correct answer in the following query

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2013-06-19 : 07:48:48

Hi,

I have two tables named customer and salesorder.

In the customer table i have 1000 customers,Of which 900 customers have orders in the salesorder table.
i execute the following query to list all customer sthat have had at least one sale.

Select * from customer where customer.CustomerID in (Select Customer.CustomerID from salesorder)

you need to identify the result of the query? which result will the query return?

1) No rows
2) A Warning message
3) The 100 rows in the customer table
4 The 900 rows in the customer table with matching rows in the salesorder table.

I am thinking the answer is 4 but some are telling that the answer is 3.So can you plese tell me the correct answer with explanation.

Thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 08:14:45

>> customer table i have 1000 customers,Of which 900 customers have orders in the salesorder table.

means 900 customers placed orders in the salesorder
(Select ~~Customer.~~CustomerID from salesorder) -- gives 900 different customerIds
--Original query
Select *
from customer
where customer.CustomerID in (Select ~~Customer.~~CustomerID from salesorder) -- made condition to check which customer had placed order

So obviously you will get 900 customers who placed orders

NOTE: IN operator checks for matching customers in the customer and salesorder tables

EDIT: I'm sorry.... I haven't seen properly that Customer.CustomerID in Sub query....
Yes, The following query returns all rows from Customer table
Select *
from customer
where customer.CustomerID in (Select Customer.CustomerID from salesorder)
--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 11:19:29

nope
all customer records(1000) will be returned

as its Customer.CustomerID inside

so for all the 1000 the condition checked would be Customer.CustomerID = Customer.CustomerID which is always true ie a trivial check and hence all of them will be returned.

see this illustration


declare @customer table
(
customerid int
)
insert @customer
values (100),(101),(105),(106)

declare @salesorder table
(
customerid int
)


insert @salesorder
values (100),(105)
--1st query (original query)
Select * from @customer c where c.CustomerID in (Select c.CustomerID from @salesorder)
--2nd query (interpreted query)
Select * from @customer c where c.CustomerID in (Select CustomerID from @salesorder)


output
----------------------------
1st query
customerid
----------------------------
100
101
105
106

second query
---------------------
customerid
---------------------
100
105

so it should be 3 which i reckon has a typo it should read

The 1000 rows in customer table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-06-19 : 12:41:05

1000 rows the way it is written, because Select Customer.CustomerID from salesorder will always equal

djj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:02:16

quote:
Originally posted by djj55

1000 rows the way it is written, because Select Customer.CustomerID from salesorder will always equal

djj

whats the point in repeating what I already illustrated?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-06-20 : 09:03:44

Yes, it is true, you illistrated it nicely. I thought this was a request for what different people thought, so added my two cents based on what I thought.

I did not mean to imply you did not answer the question already.

djj

tsaliki
Starting Member

19 Posts

Posted - 2013-06-21 : 00:25:45

@all : thanks now i got the clear cut answer with explanation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:17:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources