• 0

[MySQL] Get ONLY unique values from 2 tables?


Question

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:

Link to post
Share on other sites

5 answers to this question

Recommended Posts

  • 0
medium_pimpin

select

*

from

table.2

delete

(select

*

from

table.1

intersect

select

*

from

table.2)

Link to post
Share on other sites
  • 0
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:

Link to post
Share on other sites
  • 0
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.

Link to post
Share on other sites
  • 0
MGS4-SS

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

Link to post
Share on other sites
  • 0
+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 by mrbester
Link to post
Share on other sites
This topic is now closed to further replies.
  • Recently Browsing   0 members

    No registered users viewing this page.