Example of joins in SQLite

 Example using JOIN,AS,CROSS JOIN,JOIN ON,OUTER JOIN, Compound JOIN,Self JOIN

• First create table.
• CREATE TABLE  x ( a, b );
  INSERT INTO x VALUES ( 1, ‘Alice’ );
  INSERT INTO x VALUES ( 2, ‘Bob’ );
  INSERT INTO x VALUES ( 3, ‘Charlie’ );
• CREATE TABLE y ( c, d );
  INSERT INTO y VALUES ( 1, 3.14159 );
  INSERT INTO y VALUES ( 1, 2.71828 );
  INSERT INTO y VALUES ( 2, 1.61803 );
• CREATE TABLE z ( a, e );
  INSERT INTO z VALUES ( 1, 100 );
  INSERT INTO z VALUES ( 1, 150 );
  INSERT INTO z VALUES ( 3, 300 );
  INSERT INTO z VALUES ( 9, 900 );
• select * from x;
• | a | b     |
  | 1 | Alice    |
  | 2 | Bob    |
  | 3 | Charlie|
• select  d, d*d AS dSquared FROM y;AS|d     |dSquared
  |3.14159 |9.8695877281
  |2.71828 |7.3890461584
  |1.61803 |2.6180210809here y table create time column name is c and d. after using select command using AS change the name.JOIN,CROSS JOIN
• select * from x join y;
  select * from x cross join y;
  select * from x,y;
• |a |b     |c |d
  |1 |Alice     |1 |3.14159
  |1 |Alice     |1 |2.71828
  |1 |Alice     |2 |1.61803
  |2 |Bob     |1 |3.14159
  |2 |Bob     |1 |2.71828
  |2 |Bob     |2 |1.61803
  |3 |Charlie |1 |3.14159
  |3 |Charlie |1 |2.71828
  |3 |Charlie |2 |1.61803JOIN…ON
• select * from x join y on a=c;
  |a| b    |c| d
  |1| Alice    |1| 3.14159
  |1| Alice     |1| 2.71828
  |2| Bob     |2| 1.61803
• select * from x join z on x.a =z.a;
  |a| b     |a| e
  |1| Alice     |1| 100
  |1| Alice     |1| 150
  |3| Charlie     |3| 300OUTER JOIN
• select * from x left outer join z using (a);
  |a| b     |e
  |1| Alice     |100
  |1| Alice     |150
  |2| Bob     |[NULL]
  |3| Charlie     |300Compound JOIN
• select * from x join y on x.a = y.c left outer join z on y.c = z.a;|a| b     |c| d    | a|    e
  |1| Alice     |1| 3.14159| 1|     100
  |1| Alice     |1| 3.14159| 1|     150
  |1| Alice     |1| 2.71828| 1|     100
  |1| Alice     |1| 2.71828| 1|     150
  |2| Bob     |2| 1.61803| [NULL]| [NULL]Self JOIN
• select * from x as x1 join x as x2 on x1.a+1=x2.a;
  |a| b     |a| b
  |1| Alice     |2| Bob
  |2| Bob     |3| Charlie

For More Information Please Visit