Join Only Unique Values From 2 Tables

Question

I'm trying to join two MySQL tables, and return only the unique values between them. eg.

Table1
column1|column2
-------------
c1value1|c2value1-1
c1value2|c2value1-2
c1value3|c2value1-3

Table2
column1|column2
-------------
c1value1|c2value1-1
c1value1|c2value2-1
c1value1|c2value3-1
c1value2|c2value1-2
c1value3|c2value1-3

I want to get as my result a table that only shows the intersections of rows without any duplicates where I'm joining on table1.column1 = table2.column1:

Joined Table
table1.column2|table2.column2
-----------------------------
table1.c2value1-2|c2value1-2
table1.c2value1-3|c2value1-3

Simple joins and unions and I'm ok, but this one is causing me a headache.

Edit for clarification: I don't want any results in my joined table where Table 2 has > 1 entry in column 1. I only want to get back the values from column2 for c1value2 and c1value3.

My first thought was to get the distinct count of column2 GROUP BY column1 WHERE distinct count = 1 but that's error city.

Your desired output contains rows that are not present in your original data. Please update it. (table1.c2value1-2 is not present in Table1, but it is in your Joined Table) — Eric Ávila
@Jesse You can use the DISTINCT operator to ignore duplicates — OshiniRB

Stalinko Stalinko · Accepted Answer · 2021-09-27T09:49:38

It's not clearly stated what must be unique? If you want to generate unique pairs here you go:

SELECT DISTINCT t1.column2, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1

Join Only Unique Values From 2 Tables

1 Answers