This is the default join used by SQLite. As such the word inner can be left out of the syntax.
Similar to a cross join, but it is designed to show ONLY those elements that exist in BOTH tables.
As an example, in my books database, which authors have a book with 'astronomy' as a keyword, and what is the title?
select author,title from authors,books join keywords on authors.authorid=keywords.authorid and books.titleid=keywords.titleid and 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
A crucial condition in all database work is that your query results are correct and complete. To that end ...
select titleid,authorid from keywords where keyword like '%astronomy%' group by titleid, keyword; titleid authorid ------- -------- 355 168 442 409 660 600 770 702 select author from authors where authorid in(168,409,600,702) order by author; author ---------------------------------------- Ball,Johnny Brenner,Michael;Sörensen,Pia;Weitz,David Gibilisco,Stan Meeus,Jean select title,titleid from books where titleid in(355,442,660,770) order by title; title titleid -------------------------- ------- Astronomical Algorithms 2E 770 Astronomy Demystified 355 Science and Cooking 660 Wonders Beyond Numbers 442
That looks good.
An observant reader might wonder why the book 'Science and Cooking' was returned, when the keyword we searched for was '%astronomy%'.
That caught my eye as well so I checked. One of the keywords for that book is 'Gastronomy'.
Outer Join