SQLite

Joins 01

A common method of getting information out of a database is using joins.

Joins may be easier to understand if we visualise them using Venn diagrams - those overlapping circles with colored slices representing something belonging to each set.

The result of a join becomes the input for all subsequent operations (filtering) in the select command

There are implicit and explicit forms of a join operation.

We will be using my catalogue of digital non-fiction books. Here is the layout of the three tables used:

.schema books
create table if not exists books (
	id integer,
	titleid	integer,
	authorid integer,
	title text,
	pubyear	integer,
	filename text,
	notes text,
	isbn text,
	primary key(id autoincrement)
);

.schema authors
create table if not exists authors(
	id integer primary key autoincrement,
	author text unique not null,
	authorid integer
);

.schema keywords
create table if not exists keywords (
	id integer,
	keyword	text collate nocase,
	authorid integer,
	titleid	integer,
	primary key(id autoincrement)
);

.tables
authors books keywords sqlean_define

The table sqlean_define is created and used by SQLite. Don't mess with it.

If we want to see all book titles and authors with a particular keyword, that could be represented as follows:

All join operations can be used on 2 or more tables, and can also be used with filters for enhanced search queries.

Implicit

The implicit SQL join to do such an operation might be:

select title,author from books,authors,keywords
where books.titleid=keywords.titleid
and books.authorid=keywords.authorid
and books.authorid=authors.authorid
and keyword like '%javascript%'
order by title;

title                                                   author
------------------------------------------------------  ----------------------------------------------
Adding Ajax                                             Powers,Shelley
Advanced Ajax                                           Lauriat,Shawn M.
Ajax. The Definitive Guide                              Holdener III,Anthony T.
Data Visualization with JavaScript                      Thomas,Stephen A.
Full Stack Testing                                      Mohan,Gayathri
JavaScript Cookbook 3E                                  Scott,Adam D.;MacDonald,Matthew;Powers,Shelley
JavaScript for Web Warriors 7E                          Carey,Patrick;Vodnik,Sasha
JavaScript. The Definitive Guide 7E                     Flanagan,David
Professional JavaScript for Web Developers 5E           Frisbie,Matt
Software Engineer Learns HTML5 JavaScript & jQuery (A)  Cameron,Dane
Speaking JavaScript                                     Rauschmayer,Axel
Understanding JavaScript RegExp                         Agarwal,Sundeep
Web Application Security 2E                             Hoffman,Andrew

So where is the join command in that query?

Each table has an authorid and a titleid. Thus we can join them by using the 2 values from each table.

In this case the join is implicit, using the authorid and titleid values in a where clause.

The commands are split up onto separate lines but could also be entered on 1 line.

In Terminal if you enter commands without the closing semi-colon (;),
you will see ...>
This is a secondary continue prompt from SQLite, after hitting Enter on the line above.

select name, artists.artistid,albumid from artists
   ...> left join albums on albums.albumid=artists.artistid
   ...> order by albumid
   ...> limit 10;

Explicit

select title, author
from authors
inner join books
on authors.authorid=books.authorid
inner join keywords
on books.titleid=keywords.titleid
where keyword like '%javascript%'
order by title;

title                                                   author
------------------------------------------------------  ----------------------------------------------
Adding Ajax                                             Powers,Shelley
Advanced Ajax                                           Lauriat,Shawn M.
Ajax. The Definitive Guide                              Holdener III,Anthony T.
Data Visualization with JavaScript                      Thomas,Stephen A.
Full Stack Testing                                      Mohan,Gayathri
JavaScript Cookbook 3E                                  Scott,Adam D.;MacDonald,Matthew;Powers,Shelley
JavaScript for Web Warriors 7E                          Carey,Patrick;Vodnik,Sasha
JavaScript. The Definitive Guide 7E                     Flanagan,David
Professional JavaScript for Web Developers 5E           Frisbie,Matt
Software Engineer Learns HTML5 JavaScript & jQuery (A)  Cameron,Dane
Speaking JavaScript                                     Rauschmayer,Axel
Understanding JavaScript RegExp                         Agarwal,Sundeep
Web Application Security 2E                             Hoffman,Andrew

Depending on how your tables and database are designed, and how large it is, you may be able to execute the query implicitly, but be aware that the explicit join may be optimized better, and provide faster responses, especially if the tables are large (thousands of rows).

You should always understand the logic and purpose of your join
and choose the appropriate join type
that matches your desired output.

Cross Join

Databases with SQLite

Joins 01

Implicit

Explicit