How Does 'In' Clause Works in Oracle

How does 'in' clause works in oracle

Correct (but note that IN is an operator, not a clause and it works like this in SQL in general, not only for Oracle).

where 1 not in (null,1)

is equivalent to:

where 1 != null and 1 != 1

which should really be written as:

WHERE 1 NOT IN (NULL, 1)

and

WHERE 1 <> NULL AND 1 <> 1

which is the same as:

WHERE (1 <> NULL) AND (1 <> 1)

which evaluates to:

WHERE UNKNOWN AND FALSE

and further as:

WHERE FALSE

So, it correctly returns no rows.

---

Notice that if you had WHERE 1 NOT IN (NULL, 2), it would evaluate to WHERE UNKNOWN (left as an exercise) and no rows would be returned either.

SQL multiple columns in IN clause

You could do like this:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

The sqlfiddle.

How to put more than 1000 values into an Oracle IN clause

Put the values in a temporary table and then do a select where id in (select id from temptable)

Using LIKE in an Oracle IN clause

What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL

SELECT * 
FROM tbl
WHERE my_col LIKE ANY (ARRAY['%val1%', '%val2%', '%val3%', ...])

Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however:

SELECT * 
FROM tbl
WHERE my_col LIKE '%val1%' OR my_col LIKE '%val2%' OR my_col LIKE '%val3%', ...

Or alternatively, create a semi join using an EXISTS predicate and an auxiliary array data structure (see this question for details):

SELECT *
FROM tbl t
WHERE EXISTS (
  SELECT 1
  -- Alternatively, store those values in a temp table:
  FROM TABLE (sys.ora_mining_varchar2_nt('%val1%', '%val2%', '%val3%'/*, ...*/))
  WHERE t.my_col LIKE column_value
)

For true full-text search, you might want to look at Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

When do we use WITH clause, and what are main benefits of it?

is there any risk of using it?

Yes. Oracle may decide to materialize the subquery, which means writing its result set to disk and then reading it back (except it might not mean that in 12cR2 or later). That unexpected I/O could be a performance hit. Not always, and usually we can trust the optimizer to make the correct choice. However, Oracle has provided us with hints to tell the optimizer how to handle the result set: /*+ materialize */ to um materialize it and /*+ inline */ to keep it in memory.

I start with this potential downside because I think it's important to understand that the WITH clause is not a silver bullet and it won't improve every single query, and may even degrade performance. For instance I share the scepticism of the other commenters that the query you posted is in any way faster because you re-wrote it as a common table expression.

Generally, the use cases for the WITH clause are:

1. We want to use the result set from the subquery multiple times

   with cte as
     ( select blah from meh )
   select * 
   from t1
        join t2 on t1.id = t2.id
   where t1.col1 in ( select blah from cte )
   and   t2.col2 not in ( select blah from cte)
   

2. We want to be build a cascade of subqueries:

   with cte as
     ( select id, blah from meh )
     , cte2 as 
      ( select t2.*, cte.blah
        from cte
             join t2 on t2.id = cte.id)
     , cte3 as 
       ( select t3.*, cte2.*
         from cte2
              join t3 on t3.col2 = cte2.something ) 
      ….
   

This second approach is beguiling and can be useful for implementing complex business logic in pure SQL. But it can lead to a procedural mindset and lose the power sets and joins. This too is a risk.

3. We want to use recursive WITH clause. This allows us to replace Oracle's own CONNECT BY syntax with a more standard approach. Find out more

4. In 12c and later we can write user-defined functions in the WITH clause. This is a powerful feature, especially for users who need to implement some logic in PL/SQL but only have SELECT access to the database. Find out more

For the record I have seen some very successful and highly performative uses of the second type of WITH clause. However I have also seen uses of WITH when it would have been just as easy to write an inline view. For instance, this is just using the WITH clause as syntactic sugar ...

with cte as
  ( select id, blah from meh )
select t2.*, cte.blah 
from  t2
      join cte on cte.id = t2.id

… and would be clearer as ...

select t2.*, cte.blah 
from  t2
      join ( select id, blah from meh ) cte on cte.id = t2.id

Oracle: What does `(+)` do in a WHERE clause?

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.

Best not to use it though, for readability's sake.

Can in line views in oracle sql contain not in clause in the query?

Oracle calls subqueries in the FROM clause "inline views".

These are generic SELECT queries. They can contain NOT IN with subqueries. The problem with your query is a lack of ON clause and the use of double quotes for a string constant:

select *
from t1 inner join
     (select *
      from t2
      where t2.id not in (select ID from t2 where city = 'Paris')
---------------------------------------------------------^ single quotes
     ) t2
     on t1.? = t2.?
-----^ on clause

Note: I would discourage you from using NOT IN with subqueries, because they do not work as expected if any returned values are NULL. (If that is the case, then no rows are returned.)

I advise using NOT EXISTS instead.

Oracle SQL: How to use more than 1000 items inside an IN clause

Not sure that using so many values in a IN() is that good, actually -- especially for performances.

When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :

SELECT ...
FROM ...
WHERE ...
      AND (
          ep_codes IN (...1000 ep_codes...)
          OR  ep_codes IN (...200 ep_codes...)
      )

Does it make the results less strange ?

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.

---

Related Topics