Home / Questions / 241767
Accepted
Andres
Asked: 2020-02-29 05:07:52 +0800 CST 2020-02-29 05:07:52 +0800 CST

In SQLite how to join tables with exceptions

  • 772

Good day everyone, my question is the following, suppose I have the following two tables:

table1 id1
id2 data1 data2
---- ---- ------- -------
1 a d1a1 d2a1
1 b d1b1 d2b1
2 a d1a2 d2a2
2 b d1a2 d2a2
3 a d1a3 d2a3
3 c d1c3 d2c3

table2
id1 id2 data3 data4
---- ---- ------- -------
1 a d3a1 d4a1
null b d3b1 d4b1
null a d3a2 d4a2
2 b d3a2 d4a2

and I want to join them by doing:

SELECT t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.id1 = t2.id2 AND t1.id2 = t2.id2

This will return the following table:

id1 id2 data1 data2 data3 data4
---- ---- ------- ------- -------- --------
1 to d1a1 d2a1 d3a1 d4a1
2b d1b2 d2b2 d3b2 d4b2

What should I do to get the following table as a result:

id1 id2 data1 data2 data3 data4
---- ---- ------- ------- -------- --------
1 to d1a1 d2a1 d3a1 d4a1
null b null null d3b2 d4b2
null a null null d3a2 d4a2
2 b d1b2 d2b2 d3b2 d4b2

sqlite

1 Answers

  1. Best Answer

    This works with a left join (since sqlite doesn't support right join)

    The following query works:

    select * 
from tabla2 t2 left join tabla1 t1 on t1.id1= t2.id1 and t1.id2 = t2.id2

    Left join brings all the records from the table that is on the left (the first) even if they do not meet the join condition. It does not bring those who do not comply who are on the right (the second)

    And here is an example fiddle.

    • 1