Well, the WHERE clause filters the results. The ON just defines the way the tables are joined together. In your example there would be no difference.
But take a look at the following:
CREATE TABLE testA ( id integer, checked boolean ) CREATE TABLE testB ( id integer, testA_id integer, checked boolean )
As you can see i have added a checked column in TestB.
There might be rows in TableA that have no rows in TableB Like the following data:
TestA
ID|checked 1|true 2|true 3|false
TestB
ID|testA_id|checked 1|1|true 2|1|false 3|3|true
As you can see there is no TestB for TestA id = 2
Now, let's assume you want to display ALL TestA rows along with the checked TableB rows (if any).
So you need a left join:
First Case
SELECT tA.*, tB.* FROM testB AS tB LEFT JOIN testA AS tA ON (tA.id = tB.testA_id AND tB.checked)
Reults
ID|checked|ID|testA_id|checked 1|true|1|1|true 2|true|null|null|null 3|false||3|3|true
ID 1, 2 and 3 of TableA is returned and if there is a checked TableB row then we return that too.
Second Case
SELECT tA.*, tB.* FROM testB AS tB LEFT JOIN testA AS tA ON tA.id = tB.testA_id WHERE tB.checked
Reults
ID|checked|ID|testA_id|checked 1|true|1|1|true 3|false||3|3|true
In this case only IDs 1 and 3 are returned because we filter the results to show only the ones that have TableB checked.
