Understanding where clause vs join

Question

Can somebody explain the difference between below query examples.

select column1,column2 
from table1 
join table2 
  on table1.columna = table2.columna 
  and columna='1234'

vs

select column1,column2 
from table1 
join table2 
  on table1.columna = table2.columna 
where columna='1234'

Possible Duplicates: https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause https://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line — jmoerdyk, Jan 17 '19 at 19:50
it's not duplicated for those questions, it just sounds similar. — user2378934, Jan 17 '19 at 19:54
The first query performs filtering while joining, the second does filtering after joining. For inner joins, the result will be equivalent, unlike for outer joins. — vc 74, Jan 17 '19 at 19:58
For INNER JOIN there is no difference, the results of both queries are the same. The difference will be for LEFT/RIGHT/FULL OUTER joins, but this is not a subject of this question. — krokodilko, Jan 17 '19 at 20:41
Can you elaborate a little more about the change as what will happen as how the results will differ? — user2378934, Jan 17 '19 at 21:36

score 1 · Accepted Answer · answered Jan 18 '19 at 09:17

To answer the question in your comment, with the following tables:

table1 and table2

+---------+---------+    +---------+---------+ 
| column1 | columna |    | column2 | columna |
+---------+---------+    +---------+---------+
|      10 |    1234 |    |     100 |    1234 |
|      20 |    1234 |    |     200 |    5678 |
|      30 |    5678 |    +---------+---------+
|      40 |    9876 |
+---------+---------+

The inner join queries will produce:

+---------+---------+
| column1 | column2 |
+---------+---------+
|      10 |     100 |
|      20 |     100 |
+---------+---------+

An outer join query including the filter in the on clause will produce:

+---------+---------+
| column1 | column2 |
+---------+---------+
|      10 |     100 |
|      20 |     100 |
|      30 |         |
|      40 |         |
+---------+---------+

whereas an outer join query with the filter after the join will produce:

+---------+---------+
| column1 | column2 |
+---------+---------+
|      10 |     100 |
|      20 |     100 |
+---------+---------+

Note that since columna is in both tables, you have to indicate which columna the filter is associated to. I assumed table1's.

Thanks for the elaborative answer, I spent some time to understand it yesterday and was able to make sense out of it. I really appreciate you taking the time out for explaining it and confirming my understanding as well. — user2378934, Jan 18 '19 at 18:29

Understanding where clause vs join

1 Answers1