Joins in SQLite3

SQL to create the demo database in text file 448demo.sql:

drop table if exists student;
drop table if exists email;

create table student( pn integer, first text, last text );
create table email( pn integer, email text );

insert into student values( 50000001, 'Anthony'   , 'Taboni'   );
insert into student values( 50000002, 'Hui'       , 'Duan'     );
insert into student values( 50000003, 'Sanjeevani', 'Choudery' );
insert into student values( 50000005, 'Wenjie'    , 'Zhu'      );

insert into email values( 50000002,  'hd123@buffalo.edu' );
insert into email values( 50000003,                NULL );
insert into email values( 50000005,     'wz@buffalo.edu' );
insert into email values( 40000000,    'old@buffalo.edu' );

Run sqlite3:

$ sqlite3 448demo.db

Metacommand:

sqlite> .read 448demo.sql

Let's see what we've got:

sqlite> .tables

email    student

sqlite> select * from student;

pn          first       last
----------  ----------  ----------
50000001    Anthony     Taboni
50000002    Hui         Duan
50000003    Sanjeevani  Choudery
50000005    Wenjie      Zhu

sqlite> select * from email;

pn          email
----------  -----------------
50000002    hd123@buffalo.edu
50000003
50000005    wz@buffalo.edu
40000000    old@buffalo.edu

Full join (Cartesian product) (rarely useful)

Combines every row with every row.

sqlite> select * from student join email;

pn          first       last        pn          email
----------  ----------  ----------  ----------  -----------------
50000001    Anthony     Taboni      50000002    hd123@buffalo.edu
50000001    Anthony     Taboni      50000003
50000001    Anthony     Taboni      50000005    wz@buffalo.edu
50000001    Anthony     Taboni      40000000    old@buffalo.edu
50000002    Hui         Duan        50000002    hd123@buffalo.edu
50000002    Hui         Duan        50000003
50000002    Hui         Duan        50000005    wz@buffalo.edu
50000002    Hui         Duan        40000000    old@buffalo.edu
50000003    Sanjeevani  Choudery    50000002    hd123@buffalo.edu
50000003    Sanjeevani  Choudery    50000003
50000003    Sanjeevani  Choudery    50000005    wz@buffalo.edu
50000003    Sanjeevani  Choudery    40000000    old@buffalo.edu
50000005    Wenjie      Zhu         50000002    hd123@buffalo.edu
50000005    Wenjie      Zhu         50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo.edu
50000005    Wenjie      Zhu         40000000    old@buffalo.edu

Alternate syntax for same useless join

sqlite> select * from student, email;

pn          first       last        pn          email
----------  ----------  ----------  ----------  -----------------
50000001    Anthony     Taboni      50000002    hd123@buffalo.edu
50000001    Anthony     Taboni      50000003
50000001    Anthony     Taboni      50000005    wz@buffalo.edu
50000001    Anthony     Taboni      40000000    old@buffalo.edu
50000002    Hui         Duan        50000002    hd123@buffalo.edu
50000002    Hui         Duan        50000003
50000002    Hui         Duan        50000005    wz@buffalo.edu
50000002    Hui         Duan        40000000    old@buffalo.edu
50000003    Sanjeevani  Choudery    50000002    hd123@buffalo.edu
50000003    Sanjeevani  Choudery    50000003
50000003    Sanjeevani  Choudery    50000005    wz@buffalo.edu
50000003    Sanjeevani  Choudery    40000000    old@buffalo.edu
50000005    Wenjie      Zhu         50000002    hd123@buffalo.edu
50000005    Wenjie      Zhu         50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo.edu
50000005    Wenjie      Zhu         40000000    old@buffalo.edu

Inner (default) join

sqlite> select * from student join email on student.pn = email.pn;

pn          first       last        pn          email
----------  ----------  ----------  ----------  -----------------
50000002    Hui         Duan        50000002    hd123@buffalo.edu
50000003    Sanjeevani  Choudery    50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo.edu

Important: notice that Anthony was completely omitted because he has no entry in table email.

Natural (inner) join

The natural join implicitly requires equality of columns that have the same name.

sqlite> select * from student natural join email;

pn          first       last        email
----------  ----------  ----------  -----------------
50000002    Hui         Duan        hd123@buffalo.edu
50000003    Sanjeevani  Choudery
50000005    Wenjie      Zhu         wz@buffalo.edu

Outer left join

sqlite> select * from student outer left join email on student.pn = email.pn;

pn          first       last        pn          email
----------  ----------  ----------  ----------  ----------
50000001    Anthony     Taboni
50000002    Hui         Duan        50000002    hd123@buff
50000003    Sanjeevani  Choudery    50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo

Now Anthony is included even though he has no entry in the "right" table email.

SQLite does not support right joins, but if you need one, just permute your tables:

sqlite> select * from email outer left join student on student.pn = email.pn;

pn          email              pn          first       last
----------  -----------------  ----------  ----------  ----------
50000002    hd123@buffalo.edu  50000002    Hui         Duan
50000003                       50000003    Sanjeevani  Choudery
50000005    wz@buffalo.edu     50000005    Wenjie      Zhu
40000000    old@buffalo.edu

Where

WHERE is trickily almost a synonym for ON in joins, but not quite. So be careful: look at the difference between the following two queries.

sqlite> select * from student outer left join email on student.pn = email.pn;

pn          first       last        pn          email
----------  ----------  ----------  ----------  ----------
50000001    Anthony     Taboni
50000002    Hui         Duan        50000002    hd123@buff
50000003    Sanjeevani  Choudery    50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo
sqlite> select * from student outer left join email where student.pn = email.pn;

         first       last        pn          email
----------  ----------  ----------  ----------  -----------------
50000002    Hui         Duan        50000002    hd123@buffalo.edu
50000003    Sanjeevani  Choudery    50000003
50000005    Wenjie      Zhu         50000005    wz@buffalo.edu

So let's use ON for joins, and limit our use of WHERE to additional predicates like this:

sqlite> select * from student outer left join email on student.pn = email.pn where first like '%e%' and last like '%e%';

pn          first       last        pn          email
----------  ----------  ----------  ----------  ----------
50000003    Sanjeevani  Choudery    50000003

In

sqlite> select * from student where pn in (50000002,50000003);

pn          first       last
----------  ----------  ----------
50000002    Hui         Duan
50000003    Sanjeevani  Choudery

The expression following in can be a one-column table, possibly the result of another query.