Solved

Oracle SQL IN Operator AND OR

Posted on 2008-06-18
5
1,170 Views
Last Modified: 2013-12-12
Hi,
I have the same list of text values which are being compared to two different fields using the IN operator. I get different results if i bracket the AND OR as per below. Which one should I be using?

SELECT * FROM TABLE
WHERE FIELDA IS NOT NULL
AND FIELDB IN (ETC,ETC)
OR FIELDC IN (ETC,ETC)

or

SELECT * FROM TABLE
WHERE FIELDA IS NOT NULL
AND
(FIELDB IN (ETC,ETC)
OR FIELDC IN (ETC,ETC))

Cheers,
Brett
0
Comment
Question by:brettanderson77
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21812160
>I get different results if i bracket the AND OR as per below.
that is normal

>Which one should I be using?
depends on what result you want.

if you use AND, both fields must match, if you use OR, either field match will return the row.
0
 

Author Comment

by:brettanderson77
ID: 21812263
Hi,

Thanks for the response yet the question is not around AND/OR specifically, it is around using brackets.

Both options are using AND and OR in a similar manner yet one has the two OR options bracketed to seperate it from the first condition.

Op 1:
Where Condition 1
AND Condition 2
OR Condition 3

Op 2:
Where Condition 1
AND
 (Condition 2
OR Condition 3)

Hope you get my drift?

Cheers,
Brett
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21812461
I see.
indeed, using brackets when mixing AND and OR are important, as

Op 1:
Where Condition 1
AND Condition 2
OR Condition 3

will work like this:

Where (Condition 1
AND Condition 2)
OR Condition 3

which is indeed different as from Op2 code...

hence => rule: when having both AND and OR in a condition, put the ( ) as needed to ensure
1) the conditions are applied "correctly"
2) better readability

note: from technical term, they will be evaluated technically as they are written, it's just a what you expect might differ :)
0
 

Author Comment

by:brettanderson77
ID: 21812693
Great, thank you. So I think I was on the right track with using option 2 which will give me:

Condition 1 is true
AND
(Condition 2 OR 3 are true)

Let me know if i'm wrong!

Cheers,
Brett
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21812900
you got it!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question