Multiple columns for the same WHERE IN set

Question

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.

score 1 · Accepted Answer · answered Jan 11 '17 at 18:53

1

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

answered Jan 11 '17 at 18:53

marcothesane

6,192
1
11
21

I ended up using a variation of this technique, but creating an actual table. In my case, the set of the `WHERE IN` was very long and I wanted to discard that some problems I had were due to the length of this string making the query text too long for our connector. This approach reduced the query length and actually worked. – Sam Feb 11 '17 at 12:01
It is not the best approach to still use `OR` for this. In case you query was a bit more complex, its performance could reduce dramatically. See my answer for another approach doing the same using SET operator `intersect`. – whatsupbros May 06 '18 at 15:13

whatsupbros · Answer 2 · 2018-05-06T15:27:24.050

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:

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).
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.
Instead of selecting from dual, you can use any existing table in your schema.
This subquery integrates much easier into a complex query (and more efficiently as I already mentioned in the first point).

score 0 · Answer 3 · answered Jan 11 '17 at 18:57

0

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\]')

answered Jan 11 '17 at 18:57

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Jan 11 '17 at 19:12

0

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.

answered Jan 11 '17 at 19:12

I like the idea behind this, anyway this query won't work as expected. It only selects those rows for which the child_id column is in the set of values. – Sam Jan 26 '17 at 18:15

score 0 · Answer 5 · answered Jan 11 '17 at 20:12

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
)

score -1 · Answer 6 · edited May 23 '17 at 12:10

-1

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'));

edited May 23 '17 at 12:10

Community

1
1

answered Jan 11 '17 at 18:54

Rahul

76,197
13
71
125

1

This is not exacly what I need. For instance if for a given person `P.parent_id` *is* in the list, but `P.child_id` *is not*, that person will not be selected, despite I do want to select it. – Sam Jan 11 '17 at 18:57
This query solves a different task and it is better to be rewritten with `AND` operator. – whatsupbros May 06 '18 at 15:29

Multiple columns for the same WHERE IN set

6 Answers6