sqlite, join

$sqlite3 t.db
.CREATE TABLE d (id INT PRIMARY KEY, name TEXT );
.INSERT INTO d VALUES(1, 'sales'), (2,'r&d'), (3, 'unknown');
.CREATE TABLE e (name TEXT, d_id INT REFERENCES d(id) );
.INSERT INTO e VALUES('tom', 1), ('jerry', 2), ('jone', 4), ('smith', NULL);
.SELECT * FROM e, d;
tom|1|1|sales
tom|1|2|r&d
tom|1|3|unknown
jerry|2|1|sales
jerry|2|2|r&d
jerry|2|3|unknown
jone|4|1|sales
jone|4|2|r&d
jone|4|3|unknown
smith||1|sales
smith||2|r&d
smith||3|unknown
.SELECT * FROM e INNER JOIN d ON id=d_id;
tom|1|1|sales
jerry|2|2|r&d
.SELECT * FROM e LEFT JOIN d ON id=d_id;
tom|1|1|sales
jerry|2|2|r&d
jone|4||
smith|||
.SELECT * FROM e RIGHT JOIN d ON id=d_id;
Error: RIGHT and FULL OUTER JOINs are not currently supported
.SELECT * FROM d LEFT JOIN e ON id=d_id;
1|sales|tom|1
2|r&d|jerry|2
3|unknown||

SQLite Join - 菜鸟教程 | Join (SQL) - Detailed Pedia

But how? Three fundamental algorithms for performing a join operation exist: nested loop join, sort-merge join and hash join.

1. A nested loop [嵌套循环] join is a naive algorithm that joins two sets by using two nested loops. 
2. The key idea of the sort-merge [归并排序] algorithm is to first sort the relations by the join attribute, so that interleaved linear scans will encounter these sets at the same time.
3. Hash joins require an equijoin predicate (a predicate comparing records from one table with those from the other table using a conjunction of equality operators '=' on one or more columns). [=前面这么多修饰！]