SQL/SQLite: Left join only if multiple columns match, select fields for the join result

Question

I need to left-join two tables. The left one has about 1500 entries. These tables have three columns that must match for a correct join. I can't get it to work, though. Adding all three tables with AND produces incorrect results; a WHERE clause (after the join) doesn't return a full left join.

Furthermore, I need to keep only the three IDs and "someinfo". How would I accomplish that? Adding the field in the select produces millions of extra results. Do I have to perform the join first and then query the result?

-- unexpected results, triple joins, too many results

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2 
     ON t1.id_1 = t2.id_1 
     AND t1.id_2 = t2.id_2 
     AND t1.id_3 = t2.id_3

-- millions of results

SELECT t1.*, t2.someinfo
FROM Table1 t1
LEFT JOIN Table2 t2 
     ON t1.id_1 = t2.id_1 
     AND t1.id_2 = t2.id_2 
     AND t1.id_3 = t2.id_3

Tables:

enter image description here

Dave Bry Dave Bry · Accepted Answer · 2015-04-21T15:02:59

Are the combinations of id_1, id_2, and id_3 unique to Table2? In other words, if id_1 = 555, id_2 = 123, and id_3 = 10, those three values together only appear once in the table? That's the only reason the first query would return too many results, otherwise it should return the same data as if you did a SELECT only on Table1.

Consider this code:

declare @Table1 table (id_1 int, id_2 int, id_3 int)
declare @Table2 table (id_1 int, id_2 int, id_3 int,
    someinfo varchar(20), someotherinfo varchar(20))

insert into @Table1 (id_1, id_2, id_3) values (555, 123, 10)
insert into @Table1 (id_1, id_2, id_3) values (555, 123, 10)

insert into @Table2 (id_1, id_2, id_3, someinfo, someotherinfo)
  values (555, 123, 10, 'house', 'new')
insert into @Table2 (id_1, id_2, id_3, someinfo, someotherinfo)
  values (555, 123, 11, 'tree', 'new')
insert into @Table2 (id_1, id_2, id_3, someinfo, someotherinfo)
  values (555, 124, 1, 'car', 'new')

That will give you two tables with the following data:

Unique Data

And your first SELECT statement will produce the same results as what's in Table1

Now if we add:

insert into @Table2 (id_1, id_2, id_3, someinfo, someotherinfo)
  values (555, 123, 10, 'house', 'old')

So our table data now looks like:

Duplicate data

Your first SELECT will now produce:

Left join - duplicate data

Note how 555, 123, 10 appears twice. If you multiply this effect over 1500 rows, it will come up with a lot of (seemingly) extra data. And I think that may also be why you're seeing "millions" of results in the second query, and the reason it's different from the first is because you are now adding the someinfo field from Table2 and different text is matching up with non-unique IDs.

Good news! There is a fix! You just need to add DISTINCT to your query and it should produce the results you are expecting

SELECT DISTINCT t1.*
FROM @Table1 t1
LEFT JOIN @Table2 t2 
    ON t1.id_1 = t2.id_1 
    AND t1.id_2 = t2.id_2 
    AND t1.id_3 = t2.id_3

SELECT DISTINCT t1.*, t2.someinfo
FROM @Table1 t1
LEFT JOIN @Table2 t2 
    ON t1.id_1 = t2.id_1 
    AND t1.id_2 = t2.id_2 
    AND t1.id_3 = t2.id_3

SQL/SQLite: Left join only if multiple columns match, select fields for the join result

1 Answers