SQL & PL/SQL

f. ex.

WHERE (ColumnA = 0 AND columnB = 1) OR (ColumnA = 1 AND ColumnB = 0)

?

Hi,

a XOR is simply (A OR B) AND NOT (A AND B). You can either use this definition directly in the WHERE clause or put it in a user-defined boolean function. There is also a discussion of XOR functions at asktom site:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1284001741333

Hope this helps.

Best regards,
Nikolay

XOR means "one or the other but not both". More defination : http://en.wikipedia.org/wiki/XOR

Something like this :
There is no general xor operator in Oracle. We applies XOR operator by using something like this :

SQL> select * from test;

         A          B
---------- ----------
         5          6
         5          5
         6          6
         6          5
         3          5
         5          3


SQL> select * from test where (a=5 or b=5) and not (a=5 and b=5);

         A          B
---------- ----------
         5          6
         6          5
         3          5
         5          3

Means, select all rows where a or b = 5, but both should not = 5.

Other interested threads may be :
4166189
1511281

Regards
Girish Sharma

Thread moved from Database General to SQL and PL/SQL forum.
Please, bear in mind to ask SQL question in SQL forum :
3077

Nicolas.

Hi, Ankit,

Here's another way, which might be more efficient and easier to debug, especially when the conditions are complicated, since it only needs to evaluate each condition once:.

To find employees in the scott.emp table whose job is 'CLERK', or whose salary is less than 1275, but not both:

SELECT	ename
,	job
,	sal
FROM	scott.emp
WHERE	CASE WHEN job = 'CLERK' THEN 1 ELSE 0 END 
      + CASE WHEN sal < 1275    THEN 1 ELSE 0 END	= 1
;

Output:

ENAME      JOB              SAL
---------- --------- ----------
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
MILLER     CLERK           1300

Nice! Much easier to understand.