CODEWITHSUNDEEP
sql
beginsql
beginsql

Reputation: 135

Explanation of IN operator

I have two question concerning exercises #15 sqlZoo (List all the people who have worked with 'Art Garfunkel'.) in section 'More JOIN operations'. The solution look like this (it's not mine):

SELECT actor.name 
FROM actor 
   INNER JOIN casting ON casting.actorid = actor.id 
WHERE casting.movieid IN(
       SELECT casting.movieid FROM casting 
       WHERE casting.actorid IN(
        SELECT actor.id FROM actor 
        WHERE actor.name = 'Art Garfunkel')) 
   AND actor.name <> 'Art Garfunkel'

Almost everything looks simple but last IN condition wondering me. Let's say I will change 

WHERE casting.actorid IN(
        SELECT actor.id FROM actor 
        WHERE actor.name = 'Art Garfunkel'))

for

WHERE 
 casting.actorid = (SELECT actor.id FROM actor WHERE actor.name = 'Art Garfunkel'

Everything works fine but when I look for other available solutions, every each one use IN operator like the one above. Is there any reason for using IN operator instead of solution that I've proposed? Second question: Why I can't change (line 7) SELECT actor.id FROM actor to SELECT casting.actorid FROM casting? I mean both actor.id and casting.actorid are same (both are columns that contain actors' id). So why (in this particualr example) they are not interchangeable?

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would call it laziness. The id column in actor is -- and should be -- unique.

The use of = is actually a benefit for the SQL compiler. It clearly specifies that at most one row is to be returned by the subquery and that can -- sometimes in some databases -- result in a better execution plan.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176284

You could use = only when you are sure that subquery returns single row:

WHERE 
 casting.actorid = (SELECT actor.id FROM actor WHERE actor.name = 'Art Garfunkel')
                    -- subquery should return single row

Otherwise you will get an error:

SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

PostgreSQL ERROR: more than one row returned by a subquery used as an expression

MySQL Subquery returns more than 1 row

Upvotes: 3

Related Questions