Databases with SQLite

Joins 00

Using the 2 tables from the Now What page, we will now look at some explicit join operations.

CREATE TABLE a1 (c1 int);
INSERT INTO a1 VALUES(1);
INSERT INTO a1 VALUES(2);
INSERT INTO a1 VALUES(3);
INSERT INTO a1 VALUES(4);

CREATE TABLE a2 (c2 int);
INSERT INTO a2 VALUES(2);
INSERT INTO a2 VALUES(3);
INSERT INTO a2 VALUES(4);
INSERT INTO a2 VALUES(6);

To clarify ...

select * from a1;select * from a2;
c1
--
1
2
3
4
c2
--
2
3
4
6

Left Outer

select * from a1 left outer join a2;
c1  c2
--  --
1   4
1   2
1   3
2   4
2   2
2   3
3   4
3   2
3   3

Right Outer

select * from a1 right outer join a2;
c1  c2
--  --
1   4
1   2
1   3
2   4
2   2
2   3
3   4
3   2
3   3

Inner

select * from a1 inner join a2;
c1  c2
--  --
1   4
1   2
1   3
2   4
2   2
2   3
3   4
3   2
3   3

Cross

select * from a1 cross join a2;
c1  c2
--  --
1   4
1   2
1   3
2   4
2   2
2   3
3   4
3   2
3   3

Note that an inner join looks similar to the cross join. This will be dicussed further in Inner Join.

Joins 01