Emulate XOR in Access

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I need to execute an SQL stament with a non-bitwise XOR (Exclusive_OR)
relation.

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

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

now, 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 SQL 2005; I can do it, but probably in access is totally different:


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)


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

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

(SELECT col FROM a UNION SELECT col FROM b)
EXCEPT
(SELECT col FROM a INTERSECT SELECT col FROM b)



pleeeze, only a hint.


--
Thanks,

Rick.

"For every problem, there is a solution that is simple, neat, and wrong."
H. L. Mencken"
 
A response exists below the cited text.
How about:

SELECT col
FROM
(SELECT col
FROM a
UNION ALL
SELECT col
FROM b)
GROUP BY col
HAVING COUNT(*) = 1;

Ken Sheridan
Stafford, England
Click to expand...

That was very clever. The first thing that popped into my mind (much
less clever) was:

SELECT col
FROM (SELECT A.col FROM A UNION ALL SELECT B.col FROM B)
WHERE col IN (SELECT A.col FROM A) XOR col IN (SELECT B.col FROM B);

or for A97:

SELECT col
FROM [SELECT A.col FROM A UNION ALL SELECT B.col FROM B]. qryAandB
WHERE col IN (SELECT A.col FROM A) XOR col IN (SELECT B.col FROM B);

Note that since the XOR operation must look at both logical expressions
to complete its evaluation, putting the table with the least number of
records first to try to gain efficiency is not even worth trying.

In spite of the fact that it's possible to do set operations on the
values, SQL is not really set based, per se. Searches in SQL are really
based on the fact that the keys (or indices) can make use of set based
operations, rather than the values, for use in Rushmore Optimization.

Note that Ken's use of SELECT ALL was particularly clever because the
cases where more than one value is returned is obviated by the XOR
operation itself. I simply borrowed the ALL part for my alternate
query. I included my less clever alternate query for possible use in
creating other queries where the clever version is not as apparent, yet
I encourage SQL craftspeople to continue to seek out the more clever
versions whenever possible.

James A. Fortune
(e-mail address removed)
 
KenSheridan said:
James:

Of course your solution does have the advantage that the result can include
as many columns from A and B as desired, rather than being restricted as with
mine, by virtue of the grouping, to the one column. A further JOIN of a
UNION of A and B to my query would be required to do this, whereas with yours
the additional columns can simply be included in the outer query and first
subquery.

Ken Sheridan
Stafford, England
Click to expand...

Thanks Ken. As a very quick answer to a mostly theoretical question, I
didn't really consider the ease of adding additional fields that you
astutely point out. The only reason I bothered to post it was because
it highlighted the fact that even though SQL is set-based with respect
to keys/indices rather than to values, it is still possible, without too
much extra effort, to coerce SQL to do general set operations on table
values and return the set resulting from those operations. As someone
with a math degree, among others, I found that to be of interest.

James A. Fortune
(e-mail address removed)
 
The only problem I've found in Ken's answer is:

If you have duplicates on either table a or b (even if they are not common
values) the don't appear on the result.

--
Thanks,

Rick.

"For every problem, there is a solution that is simple, neat, and wrong."
H. L. Mencken"
 
Rick said:
The only problem I've found in Ken's answer is:

If you have duplicates on either table a or b (even if they are not common
values) the don't appear on the result.
Click to expand...

I didn't even check that situation. I really wasn't trying to play
games or be sarcastic. I would have mentioned that problem immediately.
I was just lucky that my first idea handled that case.

James A. Fortune
(e-mail address removed)
 
Rick said:
The only problem I've found in Ken's answer is:

If you have duplicates on either table a or b (even if they are not
common
values) the don't appear on the result.
Click to expand...


Astute.
 
Back
Top