ORA-00920: invalid relational operator

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-00920: invalid relational operator

Although this error is caused most likely by trivial errors, actually it gives me a chance to talk about some SQL operators whose real meaning and side effects, in my humble opinion, are everything but clearly documented.

Funnily enough, it seems that this error message has been removed altogether from the official documentation starting from version 10R1, but this doesn't mean it cannot be returned!

Let's start then from the error:

select * from user_objects
where (object_name, object_type) =
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);

ORA-00920: invalid relational operator

The problem is in that we cannot perform a comparison in this fashion because on the right hand side of the operator there isn't a single value but a list of values (a list of lists in this case).

However we can easily fix the SQL statement in this way

select * from user_objects
where (object_name, object_type) = ANY
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);

Now, if you have the PL/SQL profiler objects installed in your schema, you should get three rows as result of this query, otherwise you'll get none, but in any case, no syntax errors will be returned.

Note also that the query above is perfectly equivalent to:

select * from user_objects
where (object_name, object_type) IN
(
('PLSQL_PROFILER_RUNS', 'TABLE'),
('PLSQL_PROFILER_UNITS', 'TABLE'),
('PLSQL_PROFILER_DATA', 'TABLE')
);

Personally i tend to write SQL statements preferably using the IN clause rather than = ANY.

Now, instead of using a fixed set of allowed values, let's write a more generic query :

select * from user_objects
where (object_name) = SOME (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');

SOME is synonym of ANY, just in case you wonder what's the difference between the two operators.
Again, if the PL/SQL profiler tables are present, at least three rows will be returned.

If we change the operator in this way:

select * from user_objects
where (object_name) = ALL (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');

the query will return no rows if the PL/SQL profiler tabs are present, but it will return all user objects if they are not present in your schema.
This means that = ALL behaves like != ALL when the subquery returns no rows!
On the other hand != ALL is perfectly equivalent to NOT IN.

I can't really find = ALL suitable for any purpose off the top of my mind in this moment, i'd say that ALL is most useful when combined with operators like >= or <. For instance, it can be certainly useful to have the possibility of writing queries like:

select * from user_objects
where created > ALL
(select o.created
from user_objects o
where o.object_name like 'PLSQL%');

This query means return all objects newer than the newest object whose name begins with 'PLSQL%'.
By the way, replacing ALL with ANY (or SOME), means finding all objects newer than the oldest object named PLSQL%)

The task of finding out whether the query above is more efficient than the query given below is left to the reader as exercise :-)

select * from user_objects
where created >
(select max(o.created)
from user_objects o
where o.object_name like 'PLSQL%');

Bear in mind however that when the subquery returns no rows (no objects named PLSQL%), the former query will match all user objects, while the latter none and this will certainly have some impact on your application.

See message translations for ORA-00920 and search additional resources.