Product Documentation

Appearance

IN ​

Define a condition that compares an expression with a list of literal values or the results of a subquery.

expression [ NOT ] IN (list | subquery)

where subquery returns one column with 0 or more rows.

Note: An IN list can contain a maximum of 200000 values.

For example:

premdb=# SELECT name, city FROM team 
WHERE city in('London','Liverpool','Birmingham') ORDER BY 2,1;
        name         |    city    
----------------------+------------
 Aston Villa          | Birmingham
 Birmingham City      | Birmingham
 West Bromwich Albion | Birmingham
 Everton              | Liverpool
 Liverpool            | Liverpool
 Arsenal              | London
 Charlton Athletic    | London
 Chelsea              | London
 Crystal Palace       | London
 Fulham               | London
 Queens Park Rangers  | London
 Tottenham Hotspur    | London
 West Ham United      | London
 Wimbledon            | London
(14 rows)

The following example uses a subquery as the input to the IN condition:

premdb=# SELECT seasonid FROM season 
WHERE seasonid IN(SELECT seasonid FROM match);
 seasonid 
----------
       1
       2
       3
       4
       5
       6
       7
       8
       9
...

Note: In general, row comparisons with multi-value IN lists are supported. For example:

premdb=# select * from match where(htid, atid) not in(select htid, atid from team) limit 5;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |    2 |   52 | 0-1     | -  
       1 | 1992-08-01 00:00:00 |    2 |   55 | 0-1     | -  
       1 | 1992-08-01 00:00:00 |    2 |   63 | 2-1     | -  
       1 | 1992-08-01 00:00:00 |    2 |   64 | 3-0     | -  
       1 | 1992-08-01 00:00:00 |    2 |   65 | 3-0     | -  
(5 rows)

However, Yellowbrick does not support row comparisons for multi-value NOT IN lists when an expression on either side of the IN condition evaluates to NULL.

To work around this problem, either remove null values from the data or rewrite the query by using an EXISTS or NOT EXISTS subquery.

For example, you can rewrite this query:

select * from t1 where 
(t1.c1, t1.c2) not in (select * from t2);

as follows:

select * from t1 where 
not exists (select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2);

Parent topic:SQL Conditions