Question

MySQL does not support the difference (except) operator in SQL-99 standard, but it is known that the difference operator can be simulated using left outer join. Given two relations R(A) and S(A), assuming that the tuples are distinct, show your conceptual design and SQL implementation of R-S in MySQL using left outer join.

Can someone explain this currently reviewing

Answer 1

The except operator compares the result of 2 sets and returns distinct elements from set A that doesnot have matching element in B.

This can be done with selecting distinct elements from A, then doing LEFT OUTER JOIN with B and specifying B's value (thats used for joining) to be NULL.

SELECT DISTINCT A.id, A.this, A.that

FROM A

LEFT OUTER JOIN B ON A.id=B.id

WHERE B.id=NULL

This works because the entries that have A.id=B.id in B dont have their id NULL and by specifying B.id=NULL we omit all those entries from A, and are left with only entries from A that are not matching in B