Oracle: '= Any()' VS. 'In ()'

Oracle: '= ANY()' vs. 'IN ()'

ANY (or its synonym SOME) is a syntax sugar for EXISTS with a simple correlation:

SELECT  *
FROM    mytable
WHERE   x <= ANY
        (
        SELECT  y
        FROM    othertable
        )

is the same as:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   m.x <= o.y
        )

With the equality condition on a not-nullable field, it becomes similar to IN.

All major databases, including SQL Server, MySQL and PostgreSQL, support this keyword.

PostgreSQL =ANY and IN

That's because IN (unlike ANY) does not accept an array as input. Only a set (from a subquery) or a list of values. Detailed explanation:

• How to use ANY instead of IN in a WHERE clause with Rails?

IN vs OR of Oracle, which faster?

IN is preferable to OR -- OR is a notoriously bad performer, and can cause other issues that would require using parenthesis in complex queries.

Better option than either IN or OR, is to join to a table containing the values you want (or don't want). This table for comparison can be derived, temporary, or already existing in your schema.

Difference between IN and ANY operators in SQL

SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *
  2  FROM employee
  3  WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
  2  FROM employee
  3  WHERE salary IN (2000, 3000, 4000);

But with the IN operator you cannot use =, <>, <, >, <=, or >=

filters in where statement not working for oracle query

Add ()'s around your OR. assuming all ANDS are meant to be applied to whole set.

WHERE l.ENTITY_GRP NOT LIKE '%Wet%' 
  AND (l.ENTITY_GRP LIKE 'Implant%' or l.ENTITY_GRP LIKE 'Thermal%')
  AND s.DUE_DTTM between sysdate and sysdate +30 ;

What's happening is the OR says bring back any group like thermal regardless if it's not like wet and not within the due_dttm date range.

Using IN or JOIN or EXISTS CLAUSE

Your IN clause query is perfect.

 SELECT * FROM TABLE1
  WHERE id IN
   (
    SELECT id FROM TABLE2
    WHERE my_date = (Select max(my_date) from TABLE2)
   ) 
 ORDER BY id;

If you want an EXISTS

 WITH MY_TABLE2 AS
 (
    SELECT id FROM TABLE2
    WHERE my_date = (Select max(my_date) from TABLE2)
 )
 SELECT * FROM TABLE1 T1
 WHERE EXISTS
 ( 
    SELECT 'X' FROM MY_TABLE2  T2
    WHERE T1.id = T2.id
 )
 ORDER BY id;

If every id in table1 has only one id in table2, you can opt for JOIN,

Because EXISTS and IN would supress the duplicate ids.

 SELECT * FROM TABLE1 T1
 INNER JOIN ( 
               SELECT id FROM TABLE2
               WHERE my_date = (Select max(my_date) from TABLE2)
            ) T2
    ON (T1.id = T2.id)
 ORDER BY id;

What are the differences between Oracle SQL clause != ANY(...) and not IN (...)

I think you are misusing NOT(!) operator.

How it works:

"column_name = ANY (...)": The value must match one or more values in the
list to evaluate to TRUE.

"column_name != ANY (...)": The value must not
match one or more values in the list to evaluate to TRUE.

In your case, your column value lets say 'A' is matching with =ANY('A','B','C') but at the same time when you use !=ANY('A','B','C') then also it will evaluate to TRUE as A!=B or A!=C.

So you must use column_name !=ALL('A','B','C') or use NOT column_name =ANY('A','B','C') as following:

Either use

select * from FOO_TABLE ft where NOT ft.foo_field = any ('A','B','C'); 
 -- see the keyword NOT before column name

or

select * from FOO_TABLE ft where ft.foo_field != ALL ('A','B','C');

Cheers!!

Is there any difference between != and in Oracle Sql?

No there is no difference at all in functionality.

(The same is true for all other DBMS - most of them support both styles):

Here is the current SQL reference: https://docs.oracle.com/database/121/SQLRF/conditions002.htm#CJAGAABC

The SQL standard only defines a single operator for "not equals" and that is <>

---

Related Topics