DavidSzczesniak
2/8/2018 - 10:35 AM

ANY and IN

Evaluates if preceding values match those of the subquery its placed before. IN is an alias for it. The difference being that IN can take an expression list, ANY cannot.

any_in.sql
content_copyfile_download
\!h Example - Engineers who've been working longer than the least experienced producer:
SELECT engineer_name, years -- return engineer_name and year...
FROM engineer WHERE years > ANY -- ...where engineer.years is greater than any value of producer.years
(SELECT years FROM producer);
+---------------+-------+
| engineer_name | years |
+---------------+-------+
| George Martin |    40 | -- e.g. 40 is greater than atleast one value in producer.years
| Eddie Kramer  |    38 |
| Jeff Jarratt  |    40 |
| Ed Stasium    |    25 |
+---------------+-------+

\!h Example 2 - producers that are also engineers 
SELECT producer_name FROM producer WHERE
producer_name = ANY 
(SELECT engineer_name FROM engineer);

\!h Using IN (same result as above):
SELECT producer_name FROM producer WHERE producer_name
IN (SELECT engineer_name FROM engineer);

\!h Using NOT IN - engineers that aren't producers
SELECT engineer_name FROM engineer WHERE
engineer_name NOT IN 
(SELECT producer_name FROM producer);