All developers are familiar with finding entries based on scalar comparisons but some don’t realise that Oracle can perform comparisons involving combinations of values too.
Consider a table, named COMBINATIONS in the examples below, with two columns X and Y where each column is populated with values 1 through 10 such that all combinations are present, i.e. 100 rows. Now, the SQL to find the row where X is 2 and Y is 8 would look like:
SELECT * FROM combinations WHERE x = 2 AND y = 8
Another way of writing this is:
SELECT * FROM combinations WHERE (x,y) = ( (2,8) )
What we’ve done here is described x and y as a combination of values, i.e. (x,y), and compared that to the combination (2,8). Okay so why do this? One reason is that it’s possible to find multiple combinations of values using the IN operator, i.e.:
SELECT *
FROM combinations
WHERE (x,y) IN ( (2,8)
, (5,3)
, (6,6) )
This is a rather better way than writing something like:
SELECT * FROM combinations WHERE (x = 2 AND y = 8) OR (x = 5 AND y = 3) OR (x = 6 AND y = 6)
Furthermore, the combinations we’re looking for can come from a sub-query (assume the table SELECTED_COMBINATION has columns A and B):
SELECT *
FROM combinations c
WHERE (c.x,c.y) IN ( SELECT s.a, s.b
FROM selected_combinations s )
Not surprisingly the NOT IN operator can be used too:
SELECT *
FROM combinations
WHERE (x,y) NOT IN ( (2,8)
, (5,3)
, (6,6) )
As always when using the NOT IN operator, be very careful of NULLs. The example below returns 88 rows and not 98 as some might expect as all rows where Y is 8 get excluded:
SELECT *
FROM combinations
WHERE (x,y) NOT IN ( (NULL,8)
, (5,3)
, (6,6) )
The following returns no rows at all:
SELECT *
FROM combinations
WHERE (x,y) NOT IN ( (NULL,NULL)
, (5,3)
, (6,6) )
Mark Hoxey 