1

Assuming that we have two tables:

Table Q
D E
a 1
b 2
g 1
Table R
D E
f 1
b 2
h 3

Show the result of a set difference between tables Q and R. My answer is

D E
a 1
g 1

But my classmates said the instructor's answer is

D E
a 1
g 1
f 1
h 3

So it looks like symmetric difference rather than the real "difference", which answer is right? My classmates said that the question use the word "Between" so the answer should include two tables, but I do not think so.

3

Here is what you looking at (Set Difference)

  • Q - R
  • Set Q Minus Set R

In MySQL

SELECT Q.*
FROM Q LEFT JOIN R USING (D, E)
WHERE R.E IS NULL;

Here is what the classmates are saying (Symmetric Difference)

  • (Q - R) U (R - Q) or (Set Q Minus Set R) Union (Set R Minus Set Q)
  • Also (Set Q Union Set R) Minus (Set Q Intersection Set R)

In MySQL

SELECT A.* FROM
(SELECT Q.* FROM Q LEFT JOIN R
USING (D, E) WHERE R.E IS NULL) A
UNION
SELECT B.* FROM
(SELECT R.* FROM R LEFT JOIN Q
USING (D, E) WHERE Q.E IS NULL) B;

Please go ask Your Teacher !!!

| improve this answer | |
  • 2
    Please go ask Your Teacher !!! +1 – John Eisbrener Oct 7 '19 at 20:21
  • Hey @ypercubeᵀᴹ Thanks, I was going by D as PRIMARY KEY. (D,E) is more complete – RolandoMySQLDBA Oct 7 '19 at 21:57
0

If (D,E) in each table is set as unique by according index:

SELECT D, E
FROM ( SELECT D, E FROM Q
       UNION ALL
       SELECT D, E FROM R ) S
GROUP BY D, E
HAVING COUNT(*) = 1

If no such restriction (duplicates possible in at least one of tables):

SELECT D, E
FROM ( SELECT D, E, 'Q' tab FROM Q
       UNION ALL
       SELECT D, E, 'R' FROM R ) S
GROUP BY D, E
HAVING COUNT(DISTINCT tab) = 1
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.