Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Select Distinct from 2 different tables?

Tags:

mysql

I have 2 different tables that each have a column called product_type. How can I get the DISTINCT values of product_type across both tables? Just to clarify, if both tables have a product_type of "diamond" I only want it returned once. Basically as if both tables where combined and I selected distinct product_type from it.

Thanks!!

like image 436
JD Isaacks Avatar asked Jun 16 '09 15:06

JD Isaacks


People also ask

Is distinct better or GROUP BY?

DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY. In the following table duplicate records are present.

Can distinct be used on multiple columns MySQL?

We can use the DISTINCT clause on more than columns in MySQL. In this case, the uniqueness of rows in the result set would depend on the combination of all columns.

Can you use distinct twice?

No you can't use that, it will throw an error, but there are other alternatives where you can get your desired results.


2 Answers

Use a distinct with a subquery which contains a union

select distinct product_type from (
    select product_type from table 1
    union 
    select product_type from table 2
) t
like image 97
albertein Avatar answered Oct 06 '22 03:10

albertein


Use distinct and union:

select distinct product_type from table1
union
select distinct product_type from table2

The union will remove duplicates when combining the results.

like image 36
Guffa Avatar answered Oct 06 '22 02:10

Guffa