• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1176
  • Last Modified:

Oracle SQL IN Operator AND OR

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
brettanderson77
Asked:
brettanderson77
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
brettanderson77Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
brettanderson77Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you got it!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now