Solved

Oracle SQL IN Operator AND OR

Posted on 2008-06-18
5
1,174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

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 143

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 143

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

626 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