CODEWITHSUNDEEP
sql
Amroun
Amroun

Reputation: 455

is using 'ANY' here necessary (sql)

hey i'm new to the sql language i have a relation Movie(title, year, length, inColor, studioName, producerC#)

the question is (Which movies are longer than Gone With the Wind?)

i want to know if there a difference between these two answers and which one is correct and why

select title 
from movie 
where length>any(select length from movie where title ='gone with the wind')

select title 
from movie 
where length>(select length from movie where title ='gone with the wind')

Thanks in advance

Upvotes: 1

Views: 114

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If you know that the title is unique, then use:

select title 
from movie 
where length > (select length from movie where title = 'gone with the wind');

The use of any is a bit misleading because it implies that there could be more than one match.

If there is more than one match, I would use:

select title 
from movie 
where length > (select min(length) from movie where title = 'gone with the wind');

or:

where length > (select max(length) from movie where title = 'gone with the wind');

Depending on which I really mean.

Personally, I don't see any use for the keywords any, some, and all when used with subqueries. I think the resulting queries are easier to understand using aggregations, which make the particular comparisons explicit.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

If you have more one length for the title ='gone with the wind' then Any will help you find movies where length is greater among any one of those length in sub query.

select title 
from movie 
where length>any(select length from movie where title ='gone with the wind')

Second query will work out only if have only one length where title ='gone with the wind' else it will throw error subquery returned more than one value. It will fetch the movies where length is greater than the subquery result

select title 
from movie 
where length>(select length from movie where title ='gone with the wind')

If you have more than length for a single movie then choose the first query else use the second query.

Upvotes: 2

Mark Rotteveel
Mark Rotteveel

Reputation: 109077

length > ANY (<select>) means that length needs to be higher than one (or more) of the values returned by the select. This select could return more than one value. Replacing it with > (<select>) is only possible if you know that select to be a singleton select (ie it produces only one row).

Upvotes: 3

Related Questions