DIFFERENCE in SQL

[Larsson]

Hi!

I have read some relation algebra and there have I found an operation that I want to use in my database. It is the operation DIFFERENSE. For those who don’t know what this is comes a short explanation.
We have two sets of records A and B, they have the same set of fields (for example FNAME and LNAME). The DIFFERENSE between A and B are the records in A that is not in B. And the DIFFERENSE between B and A is the records in B that is not in A.

So can this be done in Access?

Thanks for help, Markus

 

[dalchri]

Although not directly available in Access, you can get the same effect as follows...

For a single field:
[tt]
SELECT *
FROM TableA
WHERE LName Not In (
SELECT LName
FROM TableB
)
[/tt]
For multiple fields:
[tt]
SELECT *
FROM TableA LEFT JOIN TableB
ON TableA.LName=TableB.LName And TableA.FName=TableB.FName
WHERE TableB.LName Is Null Or TableB.FName Is Null
[/tt]
Both of these examples are the DIFFERENSE between TableA and TableB. To get both the DIFFERENSE between TableA and TableB and the DIFFERENSE between TableB and TableA, you would need to do a UNION between the two queries going opposite directions.

 

[Larsson]

Thanks for the help!

 

[RickSpr]

I have one little note to add: In the WHERE clause, it's actually not necessary to test both columns for Null; testing either one will give the same results.
Rick Sprague