Here is a simple example that demonstrate usage of INNER, FULL, LEFT and RIGHT JOIN.

Create 2 tables
create table p1(pid integer, pname varchar(20));
create table p2(pid integer, ptype varchar(10));
insert some data into the tables.
insert into p1(pid, pname) values(1,'p1');
insert into p1(pid, pname) values(2, 'p2');
insert into p1(pid, pname) values(4, 'p4');
insert into p2(pid, ptype) values(1,'t1');
insert into p2(pid, ptype) values(2, 't2');
insert into p2(pid, ptype) values(3, 't3');

select * from p1;

select * from p2;

inner join is implicit. The following two produce the same result.

select * from p1,p2 where p1.pid=p2.pid;
select * from p1 inner join p2 on p1.pid=p2.pid;

select * from p1 left join p2 on p1.pid=p2.pid;

1;"p1";1;"t1"
2;"p2";2;"t2"
4;"p4";;""
select * from p1 right join p2 on p1.pid=p2.pid;

select * from p1 full join p2 on p1.pid=p2.pid;

Number of records inserted: 2008

OS: Windows XP SP2

SQLite 3.5.7

Machine: Compaq nc6230

By: Onega

Resource on DDJ:The SQLite Database Engine An embeddable SQL database with a C/C++ interface

There is a quick start in its official web site, but I think  another example(Embedding SQLite in a C program) is much better.