Finding combinations of values in SQL

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) )
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s