How to check SQL query construction with the Mimer Validator

Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.

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