Search

Top 60 Oracle Blogs

Recent comments

Subquery

How not to write subqueries:

AND     sal.ticket_airline || sal.ticket_number NOT IN (
                SELECT sub.dsd_airline || sub.dsd_ticket_number
                FROM   ...
        )

If there had been any opportunity for the optimizer to do something clever with the NOT IN, you’ve just made sure it can’t happen. On top of that you may find that you don’t get the right results – consider the following cut-n-paste:

SQL> select user from dual where 1 || 23 = 12 || 3;

USER
------------------------------
TEST_USER

1 row selected.

Sometimes people simply forget that you can have multiple columns in subqueries (or in “IN Lists”) – so it’s perfectly valid to write the subquery as:

AND     (sal.ticket_airline, sal.ticket_number) NOT IN (
                SELECT sub.dsd_airline, sub.dsd_ticket_number
                FROM   ...
        )

It’s quite likely that Oracle will actually turn this into a NOT EXISTS, or ANTI-JOIN, of course. But if it doesn’t do something nice you could try doing a manual rewrite – provided it is actually logically equivalent:

AND     not exists (
                select  null
                from    ....
                where   sub.dsd_airline    = sal.ticket_airline
                and     sub.dsd_ticket_number = sal.ticket_number
        )

Remember: NOT IN may not translate to NOT EXISTS – see also this.