Use IF statement in Oracle SQL

Question

I try to use IF ELSE statement in a sql query because of legacy application that takes user input and run the same query. Depending on the user's input I need different results. Here is what I am trying to achieve:

SELECT * FROM TABLE_A a, TABLE_B b
where a.user_id = b.user_id
AND IF
b.last_name = '' or b.last_name = NULL
THEN
a.user_id in (1, 2, 3) or a.user_name in ('John', 'Mark', 'Lucas')
ELSE
b.last_name = 'DOE'

First things first - don't use the old style join syntax, specify the join condition as in: FROM table_a a INNER JOIN table_b b ON a.user_id = b.user_id. Next - you don't need an if statement, just appropriately placed parentheses to 'group' each condition: AND (( (b.last_name = '' or b.last_name IS NULL) AND (a.user_id IN (1, 2, 3) OR a.user_name IN ('John', 'Mark', 'Lucas')) OR b.last_name = 'DOE') — Jeff
– Jeff, Commented Sep 19, 2021 at 15:06
@Jeff - are you familiar with the (non)support for empty string in Oracle SQL? It seems not; the empty string is the same as null in Oracle. — user5683823
– user5683823, Commented Sep 19, 2021 at 15:08
@mathguy - what does that have to do with this question? Regardless - checking for column = NULL is bad practice and you should use IS NULL. So what is your concern - that I changed it to IS NULL? If so - why? — Jeff
– Jeff, Commented Sep 20, 2021 at 16:58
@jeff - column = NULL is not bad practice, it is simply wrong. But that isn't what you wrote; you wrote column = '' which (to me) suggests you weren't aware that '' is the same as NULL (since you left it there, followed by OR column IS NULL). — user5683823
– user5683823, Commented Sep 20, 2021 at 17:45
@mathguy - I guess you should read the original question, which had this: b.last_name = '' or b.last_name = NULL. So all I did was change the original from = NULL to IS NULL. Of course, that has nothing to do with the question. — Jeff
– Jeff, Commented Sep 20, 2021 at 18:38

MT0 · Accepted Answer · 2021-09-19 15:02:22Z

1

IF is a PL/SQL statement; you cannot use it in SQL.

Just use AND and OR:

SELECT *
FROM   TABLE_A a
       INNER JOIN TABLE_B b
       ON a.user_id = b.user_id
WHERE  (   b.last_name IS NULL
       AND (  a.user_id in (1, 2, 3)
           OR a.user_name in ('John', 'Mark', 'Lucas')
       )
OR     b.last_name = 'DOE'

Note: In Oracle, an empty string and NULL are identical.

answered Sep 19, 2021 at 15:02

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use IF statement in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related