SQL ORs take 2

After my last post I felt like maybe I should revisit the idea. Espeically after at least one comment from a person claiming to never use ORs, which is understandable, but they refused to use UNIONs as well. Instead they opted to use UNION ALLs for everything.

The first thing I want to make clear is that ORs do have thier place. When you move into PL/SQL from standard (if you can call that) SQL ORs have a legimate place in your code much like they would any other programming language (again, if you can call PL/SQL a true programming language).

First lets clarify the differences here. A UNION will take the results of two queries, compare them, and only return to you the distinct rows. A UNION ALL will return duplicate rows if they exist within the results. So in the example I gave in my last post you wouldn’t want an UNION ALL, it would make your query return duplicates. So if you were creating a report involving invoices you’re totals could come out wrong because it might return certain invoices twice depending on how it’s connected to the rest of the data.

Enjoy the Penguins!

The dreaded OR statment (SQL)

If you’ve ever written a lot of SQL, and since taking the job I’m currently at, I spend 90% of every day writing SQL for Oracle databases. One thing I have come across is the OR statement is usually the worst the thing that can happen to your query.

Now, I do not fully understand how exactly this works behind the scenes, it is something I need to further investigate, but one would logically assume that

select *
from table_foo
where bar in('A','B')

would be equivlent to

select *
from table_foo
where bar = 'A'
or bar = 'B'

I can tell you from experience while the above example is contrived, once your query starts touching tables in large quantities the difference becomes obvious. For whatever reason this is not the case. I suppose since you’re working with a query language there is no compiler to optimize your code for you, though I wish there was. Or, if by chance, there is, it is apparently not smart enough to make the conversion for you.

Moving on, it is also possible that if using an in() statement is not an option you can also replace it with a UNION. This situation would only arise though in very limited situations, but it is still possible. For example, say we have table_foo setup like so
p_key    f_key    alpha_1
1              2              A
2              4              B
3              5              C
4              6              D

So instead of doing

select *
from table_foo foo, table_bar bar
where foo.p_key = bar.fake_key
or foo.f_key = bar.fake_key

you can instead use a UNION

select *
from table_foo foo, table_bar bar
where foo.p_key = bar.fake_key
UNION
select *
from table_foo foo, table_bar bar
where foo.f_key = bar.fake_key

by arranging you code in this way you make sure that the database is making good use of any indexes that have been created on the table. This might be a bit to difficult to apply in the real world, but if you can it will be amazing how much faster the UNION will return results as opposed to the or above it.

Enjoy the Penguins!