SQLite

Outer Join

The SQL standard defines several types of outer joins: LEFT, RIGHT, FULL.

However SQLite only supports the LEFT OUTER JOIN.

select author,title from authors,books
left outer join keywords
on authors.authorid=keywords.authorid
and books.titleid=keywords.titleid
where keyword like '%astronomy%'
group by author
order by title;

author                                    title
----------------------------------------  --------------------------
Meeus,Jean                                Astronomical Algorithms 2E
Gibilisco,Stan                            Astronomy Demystified
Brenner,Michael;Sörensen,Pia;Weitz,David  Science and Cooking
Ball,Johnny                               Wonders Beyond Numbers

The LEFT OUTER JOIN contains one instance (at least) of all rows from the left hand table.

Any unmatched rows are displayed, which makes this type of join operation useful for finding unresolved or dangling rows.

Left Join

Databases with SQLite

Outer Join