[MySQL] Get ONLY unique values from 2 tables?

[AnthonySterling]

Hey guys,

Could somebody please take a look at the following problem for me please?

I have 2 tables, and from which I return 1 column from each...

ID ID

1 1

2 2

3 3

4 4

5 5

6

7

8

9

10

Is there a way I can run ONE query to return only the non-duplicated items? (6,7,8,9,10). Currently I'm returning both sets of results with 2 queries and running them through PHP.

Most, inefficient!

Cheers,

SilverB. :blush:

[medium_pimpin]

select

*

from

table.2

delete

(select

*

from

table.1

intersect

select

*

from

table.2)

[AnthonySterling]

Cheers medium_pimpin, i'll give it a bash now!

Actually, just noticed the 'delete' command, this wont affect any data will it? :huh:

Please forgive my MySQL noobish-ness. :blush:

[AnthonySterling]

No worries, i've figured it out!

SELECT `id` FROM `table_1` WHERE `id` NOT IN(SELECT DISTINCT `id` FROM `table_2`);

Thanks again, SilverB.

[MGS4-SS]

Yeah, the Distinct command is pretty much there for that.

[+mrbester]

An exclusion join:

SELECT DISTINCT a.id
FROM a LEFT JOIN b USING (id)
WHERE b.id IS NULL

More efficient than the subquery-comparison way...

The only potential pitfall is this (and the subquery method) relies on table a having more records than table b. So long as you know which has more this isn't a problem, but if you don't then a more complicated query is required in order to properly emulate MINUS.

Edited February 13, 2008 by mrbester