Is IN syntactic sugar from = ANY?

Question

1) As far as I understand the comparison = ANY looks quite similar to IN. Is it so? For example:

SELECT * FROM HOUSES 
WHERE TYPE = ANY (SELECT TYPE FROM TYPES WHERE CAT_ID = 'PUBLIC')

Is it always equal to say?

SELECT * FROM HOUSES 
WHERE TYPE IN (SELECT TYPE FROM TYPES WHERE CAT_ID = 'PUBLIC')

2) What about NOT IN? Is it equal to <> ALL? I tend to think it is, but can't see it clearly.

Answer 1

I presume your database supports the ANY operator (some other databases do not support it). Using ANY with = followed by a subquery is logically equivalent to using IN followed by the same subquery. But the difference is that ANY also supports the other comparison operators >, >=, <, and <=.

The comparison <> ALL is saying that every record in the subquery is not equal to the value on the LHS. The negation of this is that there exists one or more records in the subquery which does equal the LHS. So, the opposite of <> ALL is just IN.

Answer 2

1. Yes, = ANY is always equal to IN.

   ANY uses OR on the values

   chosen = ANY('True', 'Yes')

   when expanded:

   chosen = 'True' OR chosen = 'Yes'

   is same as:

   chosen IN ('True', 'Yes')

   when expanded:

   chosen = 'True' OR chosen = 'Yes'

   ANY/IN both uses OR

2. Yes, NOT IN is always equal to <> ALL.

   ALL uses AND on the values

   chosen <> ALL ('Yes', 'True')

   when expanded:

   chosen <> 'Yes' AND chosen <> 'True'

   is same as:

   chosen NOT IN ('Yes', 'True')

   is same as:

   NOT (chosen IN ('Yes', 'True'))

   when expanded:

   NOT (chosen = 'Yes' OR chosen = 'True')

   Applying De Morgan Theorem:

   chosen <> 'Yes' AND chosen <> 'True'

   <> ALL/NOT IN both uses AND

I tried to google if there's a stackoverflow answer that can make the explanation "click" for me.

Found the answer on quora.

Notice, pseudo-code ahead. The query will not work out of the box. To make the query actually work, encapsulate the list to what an "Any"/"All" can operate on, e.g., an array, values

“Any” : It is equivalent to “OR” condition. Hence in below example when salary > 2000 or 3000 or 4000 is considered

SELECT empno, sal
FROM   emp
WHERE  sal > ANY (2000, 3000, 4000);

     EMPNO        SAL
---------- ----------
      7566       2975
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7902       3000

“All”: It is equivalent to “AND” condition. Hence in below example when salary > 2000 and 3000 and 4000 is considered

SELECT empno, sal
FROM   emp
WHERE  sal > ALL (2000, 3000, 4000);

     EMPNO        SAL
---------- ----------
      7839       5000

Sample data:

| empno |  ename |       job |  mgr |             hiredate |  sal | comm | deptno |
|-------|--------|-----------|------|----------------------|------|------|--------|
|  7369 |  smith |     clerk | 7902 | 1980-12-17T00:00:00Z |  800 | null |     20 |
|  7499 |  allen |  salesman | 7698 | 1981-02-20T00:00:00Z | 1600 |  300 |      0 |
|  7521 |   ward |  salesman | 7698 | 1981-02-22T00:00:00Z | 1250 |  500 |     30 |
|  7566 |  jones |   manager | 7839 | 1981-04-02T00:00:00Z | 2975 | null |     20 |
|  7654 | martin |  salesman | 7698 | 1981-09-28T00:00:00Z | 1250 | 1400 |     30 |
|  7698 |  blake |   manager | 7839 | 1981-05-01T00:00:00Z | 2850 | null |     30 |
|  7782 |  clark |   manager | 7839 | 1981-06-09T00:00:00Z | 2450 | null |     10 |
|  7788 |  scott |   analyst | 7566 | 1987-04-19T00:00:00Z | 3000 | null |     20 |
|  7839 |   king | president | null | 1981-11-17T00:00:00Z | 5000 | null |     10 |
|  7844 | turner |  salesman | 7698 | 1981-09-08T00:00:00Z | 1500 |    0 |     30 |
|  7876 |  adams |     clerk | 7788 | 1987-05-23T00:00:00Z | 1100 | null |     20 |
|  7900 |  james |     clerk | 7698 | 1981-12-03T00:00:00Z |  950 | null |     30 |
|  7902 |   ford |   analyst | 7566 | 1981-12-03T00:00:00Z | 3000 | null |     20 |
|  7934 | miller |     clerk | 7782 | 1982-01-23T00:00:00Z | 1300 | null |     10 |

Answer 3

You are right about IN being equivalent to = ANY. On this subject, the SQL:2016 standard (ISO 9075-2:2016) says:

8.4 <in predicate>

[..]

Syntax Rules

[..]

4) The expression

RVC NOT IN IPV

is equivalent to

NOT ( RVC IN IPV )

5) The expression

RVC IN IPV

is equivalent to

RVC = ANY IPV

The equivalence of IN is in point 5, and point 4 somewhat answers your question about NOT IN.