Tuesday, January 6, 2009

SQL condition on combined column

I got a strange finding on Oracle SQL today. Just take the example table below.

Column1 Column2
A B
A C
B A
B C
C A
C B

If I want to do a query to remove the third row only (B follows by A). Below is a query I would think of.

Select * from TableAbove where (column1 <> 'B' and column2 <> 'A');

Somehow the query removes all rows with A in column1 and B in column2, which is not I want. The expected result should be only the third row to be removed.

The solution I found so far, which is rather simple is to concatenate both column into one.

Select * from TableAbove where column1 || column2 <> 'BA';

The query is rather ironic because to select only a specific row, we use equal sign (e.g. column1 = 'A' and column2 = 'B'). To remove that specific row, replacing the equal sign (=) into not equal sign (<>) does not reflect the opposite behavior.

Perhaps there are some server setting to allow combined column condition queries. But for now, the concatenated column query at least solve my problem. :P