SQLite Forum

An example is easier than trying to explain it further.

```
create table T1 (id integer);
create table T2 (id integer, t text);

insert into T1 values (1), (2), (3);

insert into T2 values (2, 'hi'), (3, 'bye');

select '----';
select 'all T1';
select * from T1;

select '----';
select 'T1 inner T2: this is a cross join because no ON clause';
select * from T1 inner join T2;

select '----';
select 'T1 left T2: this is also a cross join because no ON clause';
select * from T1 left join T2;

select '----';
select 'T1 inner T2:  regular inner join, no surprises';
select * from T1 inner join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join, no surprises';
select * from T1 left join T2 on T1.id = T2.id;

select '----';
select 'T1 left T2:  regular left join with where filtering, removes rows from T1';
select * from T1 left join T2 on T1.id = T2.id where T2.t = 'hi';

select '----';
select 'T1 left T2:  regular left join with on filtering, does not remove rows from T1';
select * from T1 left join T2 on T1.id = T2.id and T2.t = 'hi';
```

Results:

```
----
all T1
1
2
3
----
T1 inner T2: this is a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 left T2: this is also a cross join because no ON clause
1|2|hi
1|3|bye
2|2|hi
2|3|bye
3|2|hi
3|3|bye
----
T1 inner T2:  regular inner join, no surprises
2|2|hi
3|3|bye
----
T1 left T2:  regular left join, no surprises
1||
2|2|hi
3|3|bye
----
T1 left T2:  regular left join with where filtering, removes rows from T1
2|2|hi
----
T1 left T2:  regular left join with on filtering, does not remove rows from T1
1||
2|2|hi
3||
```

The second last query still removes T1 rows, so if you want only conditional columns from the left join all the tests have to be in the `ON` clause.  Note that the last two queries are both reasonable/valid but they are different.  

In this simple example the predicate in the where clause of the second last query basically converted the left join to inner.  But you could imagine a more subtle predicate that would only remove some of the rows leaving some of the left-ness intact.  Basically the `WHERE` happens after the joins.  The `ON` defines the join criteria.  The QP sometimes moves things around if it is safe to do so.