After the columns containing similar items are identified, it’s time to JOIN the tables in the SQLite query so that information from both tables can be included in the final result.

There are many different types of JOINS and looking deeper into each of the tables to be joined will assist in making the best selection.

Sample SQL JOIN types:¹

LEFT JOIN: resulting rows are returned from the LEFT table even if there are no matches in the right
RIGHT JOIN: resulting rows are returned from the RIGHT table even if there are no matches in the left table
INNER JOIN: resulting rows are returned when both items are a match
FULL JOIN: resulting rows are returned when there is a match in either table

Currently, SQLite only supports the LEFT JOIN and the INNER JOIN. This means that you can join data when there is a match in every column, INNER JOIN, or join data when there is a match in only some columns, LEFT JOIN.

In our example, not every message has an attachment, so the column for the match may not always have an entry. The correct join type for this scenario is a LEFT JOIN.

Reference:

[1] https://for585.com/sqljoins