# Show the result of a set difference between tables Q and R [closed]

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.

# 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 !!!` +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

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
``````