IN
Checks whether a single value is found (or not found) within a set of values.
Syntax
(column-list) [ NOT ] IN ( values-list )
Arguments
column-list
- One or more comma-delimited columns in the queried tables.
values-list
- Comma-delimited list of constant values to find in the
column-list
columns. Eachvalues-list
value maps to acolumn-list
column according to their order invalues-list
andcolumn-list
, respectively. Column/value pairs must have compatible data types.You can specify multiple sets of values as follows:
( (
values-list
), (
values-list
)[,...] )
Null handling
Vertica supports multicolumn NOT IN subqueries where the columns are not marked NOT NULL. If one of the columns is found to contain a NULL value during query execution, Vertica returns a run-time error.
Similarly, IN subqueries nested within another expression are not supported if any column values are NULL. For example, if in the following statement column x
from either table contains a NULL value, Vertica returns a run-time error:
EXISTS versus IN
Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks. For example, the following query gets a list of all the orders placed by all stores on January 2, 2007 for vendors with records in the vendor table:
The above query looks for existence of the vendor and date ordered. To return a particular value, rather than simple existence, the query looks for orders placed by the vendor who got the best deal on January 2, 2007:
Examples
The following SELECT statement queries all data in table t11
.
The following query specifies an IN
predicate, to find all rows in t11
where columns col1
and col2
contain values of (2,3)
or (6,7)
:
The following query uses the VMart schema to illustrate the use of outer expressions referring to different inner expressions: