0

Suppose I have the following query:

SELECT a.column1,
a.column2,
b.column3
FROM table1 a
JOIN table2 b
ON a.column1 = b.column2
AND a.column2 = "value"
AND b.column3 = "other value"

Why would one ever use a WHERE when filtering the values rather than another AND, i.e.

SELECT a.column1,
a.column2,
b.column3
FROM table1 a
JOIN table2 b
ON a.column1 = b.column2
AND a.column2 = "value"
WHERE b.column3 = "other value"

Wouldn't AND always make the query faster, as it will filter out the data before the join?

bkauder
  • 123
  • 1
  • 9

3 Answers3

1

As far as I know there won't be any measurable performance difference between both the queries.

Personally i prefer to keep the Join conditions in ON clause and the filtering conditions in Where clause.

If you keep the filtering condition's in where clause it will be more readable.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

Modern rdbms query optimizers do a great job at building efficient execution plans, compare the execution plans created by your two queries, they are identical. So there will not be a performance difference.

You may find older folk who suggest there's a performance increase when adding filtering criteria to a JOIN because FROM is evaluated before WHERE, thus filtering records earlier in the process and saving time. This is just an artifact from old databases.

I agree with NoDisplayName, I usually put filtering criteria that references 1 side of the JOIN in the WHERE clause, unless needed as is sometimes the case with outer joins.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
0

Hive only supports equi-joins. So in the ON clause you can only do equality comparison:

SELECT
  ...
FROM
  ... a
JOIN
  ... b
ON
  a.column1 = b.column2
  AND a.column2 = "value"

But not:

ON
  a.column1 = b.column2
  a.column2 LIKE "value"

But you can do:

ON
  a.column1 = b.column2
WHERE
  a.column2 LIKE "value"
moonknight
  • 21
  • 2