NON BIT WISE XOR

[Rick]

In SQL 2005, I can have the following statements in order to get a
non-bitwise Exclusive-OR (XOR)relation between two tables:

Suppose I have the following tables:
a = {1, 2, 3, 4, 5, 7, 8}
b = {3, 5, 8, 10, 11, 12}

The output for a AND B in SQL = {3. 5. 8}
and the output for a OR B in SQL = {1, 2, 3, 4, 5, 7, 8, 10, 11, 12}

Now, the output for a A XOR B = {1, 2, 4, 7, 10, 11, 12}

in othe words, the output should give:
ALL but not the ones that are common (All except a AND B).

In MS-SQL 2005, I can wrtie the followings, what can be done in ACCES in
order to get the same result?

CREATE TABLE a ( col int )

GO

INSERT INTO a VALUES (1)
INSERT INTO a VALUES (2)
INSERT INTO a VALUES (3)
INSERT INTO a VALUES (4)
INSERT INTO a VALUES (5)
INSERT INTO a VALUES (7)
INSERT INTO a VALUES (8)

GO

CREATE TABLE b ( col int )

GO

INSERT INTO b VALUES (3)
INSERT INTO b VALUES (5)
INSERT INTO b VALUES (8)
INSERT INTO b VALUES (10)
INSERT INTO b VALUES (11)
INSERT INTO b VALUES (12)

GO

-- a AND B in SQL = {3. 5. 8}
SELECT col
FROM a
INTERSECT
SELECT col
FROM b

GO

-- a OR B in SQL = {1, 2, 3, 4, 5, 7, 8, 10, 11, 12}
SELECT col
FROM a
UNION
SELECT col
FROM b

GO

-- a XOR B in SQL = {1, 2, 4, 7, 10, 11, 12}
(SELECT col FROM a UNION SELECT col FROM b)
EXCEPT
(SELECT col FROM a INTERSECT SELECT col FROM b)

--
Thanks,

Rick.

"For every problem, there is a solution that is simple, neat, and wrong."
H. L. Mencken"

 

[Tom van Stiphout]

On Fri, 30 Oct 2009 14:36:01 -0700, Rick

An EXCEPT clause can be rewritten using WHERE NOT IN ()

-Tom.
Microsoft Access MVP