Solved

Oracle SQL IN Operator AND OR

Posted on 2008-06-18
5
1,168 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.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now