CODEWITHSUNDEEP
sqlsqlite
Tester101
Tester101

Reputation: 8172

Is it possible to EXCEPT two unioned selects?

I've got two unioned select statements like this

SELECT value1 FROM table1 WHERE value2 = 'the'
UNION
SELECT value1 FROM table1 WHERE value2 = 'quick'

Then another two unioned select statements like this

SELECT value1 FROM table1 WHERE value2 = 'brown'
UNION
SELECT value1 FROM table1 WHERE value2 = 'fox'

I'd like to combine the results of these two queries using EXCEPT. Is this possible? How would I do it? If at all possible, I'd prefer to avoid temp tables or other complexities.

I've tried simply sticking EXCEPT in between the two groups, but that obviously didn't work. Then I tried Putting each group in parenthesis, and adding EXCEPT between them.

I know it may seem basic to some folks, but I don't do much SQL, so it's not as obvious to me.

If it makes any difference, I'm using SQLite.

Upvotes: 0

Views: 50

Answers (2)

CL.
CL.

Reputation: 180162

You want to remove the results from the two last queries from the overall result, so you can use EXCEPT with both of them; in other words, (A + B) − (C + D) = A + B − C − D:

SELECT value1 FROM table1 WHERE value2 = 'the'
UNION
SELECT value1 FROM table1 WHERE value2 = 'quick'
EXCEPT
SELECT value1 FROM table1 WHERE value2 = 'brown'
EXCEPT
SELECT value1 FROM table1 WHERE value2 = 'fox'

Upvotes: 1

Tester101
Tester101

Reputation: 8172

So apparently this is not as straightforward in SQLite, which is why I was having trouble.

Instead of doing this...

(SELECT value1 FROM table1 WHERE value2 = 'the'
UNION
SELECT value1 FROM table1 WHERE value2 = 'quick')

EXCEPT

(SELECT value1 FROM table1 WHERE value2 = 'brown'
UNION
SELECT value1 FROM table1 WHERE value2 = 'fox')

Whick works in SQL server and other places, you have to do this...

SELECT * FROM 
(SELECT value1 FROM table1 WHERE value2 = 'the'
UNION
SELECT value1 FROM table1 WHERE value2 = 'quick')

EXCEPT

SELECT * FROM 
(SELECT value1 FROM table1 WHERE value2 = 'brown'
UNION
SELECT value1 FROM table1 WHERE value2 = 'fox')

Thank you Andriy M, for providing this answer. Which explained that parenthesis don't work the same in SQLite, and provided a workaround.

Upvotes: 0

Related Questions