SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find the correct answer in the following query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tsaliki
Starting Member

India
19 Posts

Posted - 06/19/2013 :  07:48:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 06/19/2013 :  08:14:45  Show Profile  Reply with Quote
>> 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

Edited by - bandi on 06/20/2013 09:22:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/19/2013 :  11:19:29  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/19/2013 11:29:21
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 06/19/2013 :  12:41:05  Show Profile  Reply with Quote
1000 rows the way it is written, because Select Customer.CustomerID from salesorder will always equal

djj
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/20/2013 :  01:02:16  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 06/20/2013 :  09:03:44  Show Profile  Reply with Quote
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
Go to Top of Page

tsaliki
Starting Member

India
19 Posts

Posted - 06/21/2013 :  00:25:45  Show Profile  Reply with Quote
@all : thanks now i got the clear cut answer with explanation.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:17:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000