Anthony Anthony - 23 days ago 6
SQL Question

How to left join on two tables on just unique ids

I have two tables

Table 1:

color_id | label
---------|------
2 | 0
3 | 0
2 | 0
1 | 0
4 | 1
4 | 1
5 | 0


Table 2:

color_id
--------
2
1
4


I want a query that just gives me results for color_ids that are present in Table 2

So, I wrote:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.color_id = table2.color_id
WHERE table2.color_id IS NOT NULL


however, the above gives duplicates as well. Meaning I get

2 | 0
2 | 0
1 | 0
4 | 1
4 | 1


I don't want the duplicates in the results. I just want unique items.

Answer

When you add the keyword Left (or Right or full) to a join specifier, you make the join an outer join. This means that you get all the rows from one side of the join, and only those rows from the other side that match. If you only want the rows from table_1 where the color_id is in table_2, then you want an inner join, specified by writing inner join or just writing join, without a left, right or full.

to eliminate duplicates, add the keyword distinct to the select clause...

Select distinct color_id, label
From table1 t1
  join table2 t2
     on t2.color_id = t1.color_id