Using And operator with subquery

问题:

Find the sid who have reserved a red and a green boat.

Table reserves

sid    bid
22     101
22     102
22     103
31     103
32     104

Table Boats

Bid   Color
101   blue
102   red
103   green
104   red

Is it possible to use and operator with sub query something like this

select sid from reserves where bid in
(select bid from boats where color='red') and 
(select bid from boats where color='green') ;

Here I need check whether "bid" is present in the results of both 1st and 2nd sub query then select the sid.It doesn't considers the result of second sub query result for me though.

回答1:

You need to specify in the bin in against both subsqueries, and as you would both red and green boats (and boats can only be one colour) you are actually saying you would like the reservers that are either red or green, so OR is appropriate here.

select sid from reserves where 
bid in (select bid from boats where color='red') 
OR 
bid in (select bid from boats where color='green') ;

But a more efficient way to do this is not with two subqueries but with a join:

SELECT sid FROM reserves AS r
LEFT JOIN Boats as b ON r.bid = b.bid
WHERE b.color IN ('red', 'green')

If you only want the list to contain unduplicated SIDs you can use either of the following:

SELECT distinct(sid) FROM reserves AS r
LEFT JOIN Boats as b ON r.bid = b.bid
WHERE b.color IN ('red', 'green')

or

SELECT sid FROM reserves AS r
LEFT JOIN Boats as b ON r.bid = b.bid
WHERE b.color IN ('red', 'green')
GROUP BY sid

I have had mixed experience with the efficiency of GROUP BY vs DISTINCT on really big tables (40GB+)

UPDATE: As I miss understood your previous question this maybe a more suitable solution:

SELECT sid FROM reserves AS r
LEFT JOIN Boats as b ON r.bid = b.bid
GROUP BY sid
HAVING sum(b.color = 'red') and sum(b.color= 'green') 

Here we are joining the tables, then grouping the rows by the SID. Using the having clause we are counting the sum of the boolean checks on b.color = 'red' (and 'green') the sum will be zero if you dont have any bids boats that are red (or green) and by anding those together you know that the sum of reds > 1 and sum(green) >1.

And a sqlfiddle for you to play with: http://sqlfiddle.com/#!2/b5ec1/8

回答2:

Try like This

SELECT sid 
FROM reserves 
WHERE bid IN
(select bid FROM boats WHERE color IN ('red','green'))

(Or)

SELECT sid 
FROM reserves 
WHERE bid IN
(select bid FROM boats WHERE color = 'red' OR color = 'green')

回答3:

I would write

select sid from reserves where bid in
(select bid from boats where color='red' OR color='green');

回答4:

It works for something like this

select sid from reserves where bid in(select bid from boats where color='red') 
and sid in
(select sid from reserves where bid in(select bid from boats where color='green'));

回答5:

You might also consider this approach (replacing the subquery with a join):

SELECT sid
FROM reserves
JOIN boats ON
reserves.bid = boats.bid
AND boats.color IN ('red', 'green')

This query returns all sid in reserves that have a boats entry with the same bid with one of the specified colors.