CODEWITHSUNDEEP
sqloracle-database
Sam
Sam

Reputation: 1232

Multiple columns for the same WHERE IN set

Let's suppose I want to find out all the people who are either parent or child of an specific set of people.

I could do something like:

SELECT *
FROM people P
WHERE
  P.parent_id IN ('111', 'abc', '42', '1a2b3c') OR
  P.child_id IN  ('111', 'abc', '42', '1a2b3c')

Is there any way in which I could avoid writing the list twice (or more times if I were looking for more columns)?

I'm looking for something like:

(...) WHERE (P.parent_id OR P.child_id) IN ('111', 'abc', '42', '1a2b3c')

I'm using Oracle, but a plain SQL solution would be appreciated too.

Upvotes: 1

Views: 1818

Answers (6)

whatsupbros
whatsupbros

Reputation: 802

In terms of performance and universality, it's better to use the other approach than OR for such a case. In Oracle, there are SET operators which could help you very much. For example for your case, your query could look like this:

select *
from people p
where
    exists (
        (
            select p.parent_id from dual
            union all
            select p.child_id from dual
        ) intersect (
            select '111' from dual
            union all
            select 'abc' from dual
            union all
            select '42' from dual
            union all
            select '1a2b3c' from dual
        )
    )

Or using with clause:

with people_list (value) as  (
            select '111' from dual
            union all
            select 'abc' from dual
            union all
            select '42' from dual
            union all
            select '1a2b3c' from dual
) 
select * from people p
where
    exists (
        (
            select p.parent_id from dual
            union all
            select p.child_id from dual
        ) intersect (
            select value from people_list
        )
    )

Benefits of such an approach:

  1. It is better in terms of performance (as it was already mentioned, it is hard for optimizer to digest ORs in queries, especially when they are a bit more complex).
  2. It is more universal - indeed, you can add as many columns as you want to be checked if they are in the given set of values.
  3. Instead of selecting from dual, you can use any existing table in your schema.
  4. This subquery integrates much easier into a complex query (and more efficiently as I already mentioned in the first point).

Upvotes: 1

Mohammed Elshennawy
Mohammed Elshennawy

Reputation: 967

CREATE TABLE #People(parent_id NVARCHAR(50),child_id NVARCHAR(50))
GO
INSERT INTO #People
    ( parent_id, child_id )
VALUES  ( N'111', -- parent_id - nvarchar(50)
      N'321331'  -- child_id - nvarchar(50)
      ),( N'111', -- parent_id - nvarchar(50)
      N'abc'  -- child_id - nvarchar(50)
      ),( N'42', -- parent_id - nvarchar(50)
      N'321331'  -- child_id - nvarchar(50)
      ),( N'111', -- parent_id - nvarchar(50)
      N'1a2b3c'  -- child_id - nvarchar(50)
      ),( N'11dsdfs1', -- parent_id - nvarchar(50)
      N'1a2sdfsdfsb3c'  -- child_id - nvarchar(50)
      )
;WITH CTE (Value) AS (
      SELECT '111'    
     UNION SELECT 'abc'   
     UNION SELECT '42'    
     UNION SELECT '1a2b3c' 
  ) 
SELECT *
FROM #People p
WHERE EXISTS(
    (SELECT p.parent_id 
    UNION 
    SELECT p.child_id
    )
    INTERSECT 
    SELECT value
    FROM CTE
)

Upvotes: 0

user5683823
user5683823

Reputation:

One way is with a hierarchical query (and then it's more general - you can use different cutoffs by LEVEL):

select *
from   people
connect by level <= 2
       and parent_id = prior child_id
start with child_id in ( ..... )

It would still be best to have the "search id's" in a separate table, and the IN condition as in (select search_id from helper_table) as shown in another answer.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You are already using OR, so the query is hard to optimize. Hence, regular expressions are an option:

SELECT *
FROM people P
WHERE regexp_like('[' || P.parent_id || ']['  || p.child_id || ']') regexp_like('\[111|abc|42|1a2b3c\]')

Upvotes: 0

Rahul
Rahul

Reputation: 77896

You can probably group them using something like below [Brevity: idea taken from SQL multiple columns in IN clause

WHERE (P.parent_id, P.child_id)  IN (('111','111'), ('abc','abc'),('42','42'), ('1a2b3c','1a2b3c'));

Upvotes: -1

marcothesane
marcothesane

Reputation: 6749

Try this:

WITH search_ids (id) AS (
          SELECT '111'    FROM dual
UNION ALL SELECT 'abc'    FROM dual
UNION ALL SELECT '42'     FROM dual
UNION ALL SELECT '1a2b3c' FROM dual
) 
SELECT * FROM people P 
WHERE P.parent_id IN (SELECT id FROM search_ids)
  OR  P.child_id IN (SELECT id FROM search_ids)
;

The FROM dual bit is Oracle specific.

Happy playing

Marco

Upvotes: 1

Related Questions