Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find the correct answer in the following query Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:46 AM
Points: 18, Visits: 157
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
Post #1465103
Posted Wednesday, June 19, 2013 5:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,612, Visits: 4,709
Are you in the middle of some exam or is your exam over?

This does not seem to be some real time scenario that you are facing in your assignment.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465112
Posted Wednesday, June 19, 2013 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:46 AM
Points: 18, Visits: 157
No i am planning to take the exam.So preparing.If you know the answer can you clear my doubt?


Thank you
Post #1465114
Posted Wednesday, June 19, 2013 6:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:52 AM
Points: 383, Visits: 602
you will get an error message

Select Customer.CustomerID from salesorder

table Customer has not been defined in the subquery.

The subquery should read

Select salesorder.CustomerID from salesorder

or
Select CustomerID from salesorder

There is no need to do a subquery, you could (and should) use a join

Select distinct 
C.CustomerID
FROM
Customer C
INNER JOIN
salesorder S on S.customerID = C.CustomerID

Post #1465119
Posted Wednesday, June 19, 2013 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,612, Visits: 4,709
aaron.reese (6/19/2013)
you will get an error message

Select Customer.CustomerID from salesorder

table Customer has not been defined in the subquery.


Are you sure? Check this

CREATE TABLE customer
(
customerid INT
)

CREATE TABLE salesorder
(
customerid INT
)

INSERT customer
SELECT 1 UNION ALL
SELECT 2

INSERT salesorder
SELECT 1

SELECT * FROM customer WHERE customer.customerid IN (SELECT customer.customerid FROM salesorder)

DROP TABLE customer
DROP TABLE salesorder

The query will return all the rows from the customer table



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465131
Posted Wednesday, June 19, 2013 6:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:08 AM
Points: 12,864, Visits: 31,711
also, modify Kingstons' execellent example with this:
INSERT	salesorder
SELECT 3 UNION
SELECT NULL

and the query will return nothing, because the IN() list must be all non null values, otherwise, nothing gets returned.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1465133
Posted Wednesday, June 19, 2013 6:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:40 PM
Points: 42,335, Visits: 35,391
iiit.raju (6/19/2013)
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.


Why don't you create and populate the tables, run the query and see which it is?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465134
Posted Wednesday, June 19, 2013 6:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:52 AM
Points: 383, Visits: 602
interesting (and worrying!)

SELECT * FROM customer WHERE customer.customerid in (SELECT customer.customerid FROM salesorder)

If you just run the subquery, then you get an error.

The answer you get (two records) is wrong by any standard as customer ID 2 does not exist in the subquery table

Interestingly, if you drop the table name from the subquery

SELECT * FROM customer WHERE customer.customerid in (SELECT customerid FROM salesorder)

you get the right answer


Any idea why the first query does not throw an error?
Post #1465137
Posted Wednesday, June 19, 2013 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:40 PM
Points: 42,335, Visits: 35,391
aaron.reese (6/19/2013)
Any idea why the first query does not throw an error?


Because when you have a subquery the tables in both the outer query and subquery are in scope. It's not worrying, it's essential for correlated subqueries (and it's documented behaviour)

The query is probably written incorrectly, but the behaviour its showing (the two rows) is correct for the way the query is written.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465140
Posted Wednesday, June 19, 2013 7:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,612, Visits: 4,709
SELECT * FROM customer WHERE customer.customerid in (SELECT customer.customerid FROM salesorder)

In the above query, the term customer.customerid references the customer table outside as the customer table is still in scope
For all rows in customer table, the customerid will always match the same customerid and hence, the 2 rows are displayed as result

(SELECT customerid FROM salesorder)

In the above query the column customerid not preceded with table name as customer and hence, it becomes equivalent to salesorder.customerid
This will give you the correct result

As Gail has stated, this is documented behavior of correlated subqueries and there is nothing to worry about



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465142
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse