Query workaround in order to avoid IN Operator in WHERE Clause, PostgreSQL

Query workaround in order to avoid IN Operator in WHERE Clause

--PostgreSQL 9.6
--'\\' is a delimiter

select version() as postgresql_version;

-- query workaround in order to avoid IN Operator in WHERE Clause

-- some simple table to demonstrate the IN Operator Workaround
create table sourceOfInData (data char(1));

-- some simple data to be filtered
insert into  sourceOfInData 
       values ('a'),('c'),('b'),('k'),('t'),('d'),
              ('t'),('C'),('1'),('S'),('Q'),('L'), 
              ('h'),('b'),('m'),('n'),('B'),('T'),
              ('Z'),('z'),('.'),(','),('c'),('C');
                             
-- all the inserted data
select * from sourceOfInData;

-- what we want to avoid
select * from sourceOfInData where data in ('a', 'c');

-- the workaround with same resault
select s.* from sourceOfInData s join (
select 'a' as SameAsInOperator
 union
select 'c') x on s.data = x.SameAsInOperator;

-- other example
select s.* from sourceOfInData s join (
select 'S' as SameAsInOperator
 union
select 'Q' union select 'X' --'missing'
 union
select 'L') x on s.data = x.SameAsInOperator;

-- clean up
drop table sourceOfInData;


run \| edit \| history \| help	0

Please log in to post a comment.

stackse - search stackoverflow differently