Joins in SQLite3

Create a demo database by copying the following into a text file called 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 by entering the following into a terminal:

sqlite3 448demo.db

Metacommand

sqlite> .read 448demo.sql

Let's see what we've got.

1. What is the output of the following?

sqlite> .tables

answer:

email    student

2. What is the output of the following?

sqlite> SELECT * FROM student;

 answer:

50000001|Anthony|Taboni
50000002|Hui|Duan
50000003|Sanjeevani|Choudery
50000005|Wenjie|Zhu

3. What is the output of the following?

sqlite> SELECT * FROM email;

answer:

50000002|hd123@buffalo.edu
50000003|
50000005|wz@buffalo.edu
40000000|old@buffalo.edu

Different types of joins

Go through the steps below, and copy the answers in a notepad or text edit.

Full join (Cartesian product) (rarely useful)

This combines every row with every row.

4. What is the output of the following?

sqlite> SELECT * FROM student join email;

Alternate syntax for same useless join

5. What is the output of the following?

sqlite> SELECT * FROM student, email;

Inner (default) join

6. What is the output of the following?

sqlite> SELECT * FROM student JOIN email ON student.pn = email.pn;

\textbf{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.

7. What is the output of the following?

sqlite> SELECT * FROM student natural JOIN email;

Outer left join

sqlite> SELECT * FROM student OUTER LEFT JOIN email ON student.pn = email.pn;

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:

8. What is the output of the following?

sqlite> SELECT * FROM email OUTER LEFT JOIN student ON student.pn = email.pn;