Suppose I have a table with columns as (a,b,c,d) and (a,b) form the composite key. Can I write the query with values of the key in a list of values? For example, like this:
select a,c,d from mytable where (a,b) in ((1,2),(1,4),(1,5))
Should you ever be in doubt as to whether some SQL construction is valid, run it through the Mimer Validator. In this instance, your query—entered into the validator exactly as you wrote it—produces the following results:
In SQL-92, it is invalid
In SQL-99 and SQL_2003, it is valid, with these comments:
The following features outside Core SQL-99, or Core SQL-200x (draft), are used:
F641, "Row and table constructors"
T051, "Row types"
F561, "Full value expressions"
What this means is that if your particular database system does not support those optional features, then you will still get an error message.
You might want to try the following alternative query:
SELECT a,c,d FROM mytable WHERE a = 1 AND b = 2 OR a = 1 AND b = 4 OR a = 1 AND b = 5
This works quite well because (a,b) is a key, so index lookups will be efficient.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
SQL expert Rudy Limeback explains how to use string functions to make an SQL join using only a portion of a column value. Continue Reading