SQL is selected when one condition is met, but not both

Question

Given the following table:

id column1 column2 ------------------------- 1 3 8 2 4 7 3 4 10 4 4 14 5 14 17 6 10 27 7 14 21 8 16 14 9 21 4 10 30 3 

What is the best way to query for row selection when numbers 4 and 14 are selected in column1 or column2 , but exclude when numbers 4 and number 14 are in a string. Please note that the order may be canceled.

expected output

 id column1 column2 ------------------------- 2 4 7 3 4 10 5 14 17 7 14 21 8 16 14 9 21 4 

Answer 1

 SELECT * FROM table WHERE (column1=4 XOR column2=14) XOR (column1=14 XOR column2=4) 

Answer 2

Try the following:

 SELECT * FROM mytable WHERE ((column1 = 4 AND column2 != 14) OR (column2 = 14 AND column1 != 4) OR (column1 = 14 AND column2 != 4) OR (column2 = 4 AND column1 != 14)) 

Answer 3

 SELECT id, column1, column2 FROM table WHERE column1 in ('4','14') AND column2 NOT IN ('4','14') OR column2 in ('4','14') AND column1 NOT IN ('4','14') 

Answer 4

I don't know if this is suitable, but this should work:

 SELECT * FROM t WHERE ( column1 IN (4,14) AND column2 NOT IN (4,14) ) OR ( column1 NOT IN (4,14) AND column2 IN (4,14) ) 

Answer 5

 with cte as( SELECT * FROM t WHERE column1 in(4,14) or column2 in(4,14) ) SELECT * FROM t WHERE (column1 in(4,14) or column2 in(4,14)) and id not in(select id from cte where column1 in(4,14) and column2 in(4,14)) 

Answer 6

SELECT * FROM t WHERE column1 in (4,14) or column2 in (4,14) Except SELECT * FROM t WHERE column1 in (4,14) and column2 in (4,14)