SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

On INNER JOINs they are interchangeable, and the optimizer will rearrange them at will.

On OUTER JOINs, they are not necessarily interchangeable, depending on which side of the join they depend on.

I put them in either place depending on the readability.

The way I do it is:

• Always put the join conditions in the ON clause if you are doing an INNER JOIN. So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause.

• If you are doing a LEFT JOIN, add any WHERE conditions to the ON clause for the table in the right side of the join. This is a must, because adding a WHERE clause that references the right side of the join will convert the join to an INNER JOIN.

  The exception is when you are looking for the records that are not in a particular table. You would add the reference to a unique identifier (that is not ever NULL) in the RIGHT JOIN table to the WHERE clause this way: WHERE t2.idfield IS NULL. So, the only time you should reference a table on the right side of the join is to find those records which are not in the table.

On an inner join, they mean the same thing. However you will get different results in an outer join depending on if you put the join condition in the WHERE vs the ON clause. Take a look at this related question and this answer (by me).

I think it makes the most sense to be in the habit of always putting the join condition in the ON clause (unless it is an outer join and you actually do want it in the where clause) as it makes it clearer to anyone reading your query what conditions the tables are being joined on, and also it helps prevent the WHERE clause from being dozens of lines long.

Let's consider those tables :

A

id | SomeData

B

id | id_A | SomeOtherData

id_A being a foreign key to table A

Writting this query :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;

Will provide this result :

/ : part of the result
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+

What is in A but not in B means that there is null values for B.

Now, let's consider a specific part in B.id_A, and highlight it from the previous result :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+---+///|                         |
|/////////////////////|***|///|                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Writting this query :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;

Will provide this result :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|       |                         |
|/////////////////////|       |                         |
|/////////////////////+---+   |                         |
|/////////////////////|***|   |                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Because this removes in the inner join the values that aren't in B.id_A = SpecificPart

Now, let's change the query to this :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;

The result is now :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|                     |       |                         |
|                     |       |                         |
|                     +---+   |                         |
|                     |***|   |                         |
|                     +---+---+-------------------------+
|                             |
+-----------------------------+

Because the whole result is filtered against B.id_A = SpecificPart removing the parts B.id_A IS NULL, that are in the A that aren't in B

In terms of the optimizer, it shouldn't make a difference whether you define your join clauses with ON or WHERE.

However, IMHO, I think it's much clearer to use the ON clause when performing joins. That way you have a specific section of you query that dictates how the join is handled versus intermixed with the rest of the WHERE clauses.

Are you trying to join data or filter data?

For readability it makes the most sense to isolate these use cases to ON and WHERE respectively.

• join data in ON
• filter data in WHERE

It can become very difficult to read a query where the JOIN condition and a filtering condition exist in the WHERE clause.

Performance wise you should not see a difference, though different types of SQL sometimes handle query planning differently so it can be worth trying ¯\_(ツ)_/¯ (Do be aware of caching effecting the query speed)

Also as others have noted, if you use an outer join you will get different results if you place the filter condition in the ON clause because it only effects one of the tables.

I wrote a more in depth post about this here: https://dataschool.com/learn/difference-between-where-and-on-in-sql

I think this distinction can best be explained via the logical order of operations in SQL, which is, simplified:

• FROM (including joins)
• WHERE
• GROUP BY
• Aggregations
• HAVING
• WINDOW
• SELECT
• DISTINCT
• UNION, INTERSECT, EXCEPT
• ORDER BY
• OFFSET
• FETCH

Joins are not a clause of the select statement, but an operator inside of FROM. As such, all ON clauses belonging to the corresponding JOIN operator have "already happened" logically by the time logical processing reaches the WHERE clause. This means that in the case of a LEFT JOIN, for example, the outer join's semantics has already happend by the time the WHERE clause is applied.

I've explained the following example more in depth in this blog post. When running this query:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

The LEFT JOIN doesn't really have any useful effect, because even if an actor did not play in a film, the actor will be filtered, as its FILM_ID will be NULL and the WHERE clause will filter such a row. The result is something like:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
194       MERYL       ALLEN      1
198       MARY        KEITEL     1
30        SANDRA      PECK       1
85        MINNIE      ZELLWEGER  1
123       JULIANNE    DENCH      1

I.e. just as if we inner joined the two tables. If we move the filter predicate in the ON clause, it now becomes a criteria for the outer join:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
  AND film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

Meaning the result will contain actors without any films, or without any films with FILM_ID < 10

ACTOR_ID  FIRST_NAME  LAST_NAME     COUNT
-----------------------------------------
3         ED          CHASE         0
4         JENNIFER    DAVIS         0
5         JOHNNY      LOLLOBRIGIDA  0
6         BETTE       NICHOLSON     0
...
1         PENELOPE    GUINESS       1
200       THORA       TEMPLE        1
2         NICK        WAHLBERG      1
198       MARY        KEITEL        1

In short

Always put your predicate where it makes most sense, logically.

They are equivalent, literally.

In most open-source databases (most notable examples, in MySql and postgresql) the query planning is a variant of the classic algorithm appearing in Access Path Selection in a Relational Database Management System (Selinger et al, 1979). In this approach, the conditions are of two types

• conditions referring to a single table (used for filtering)
• conditions referring to two tables (treated as join conditions, regardless of where they appear)

Especially in MySql, you can see yourself, by tracing the optimizer, that the join .. on conditions are replaced during parsing by the equivalent where conditions. A similar thing happens in postgresql (though there's no way to see it through a log, you have to read the source description).

Anyway, the main point is, the difference between the two syntax variants is lost during the parsing/query-rewriting phase, it does not even reach the query planning and execution phase. So, there's no question about whether they are equivalent in terms of performance, they become identical long before they reach the execution phase.

You can use explain, to verify that they produce identical plans. Eg, in postgres, the plan will contain a join clause, even if you didn't use the join..on syntax anywhere.

Oracle and SQL server are not open source, but, as far as I know, they are based equivalence rules (similar to those in relational algebra), and they also produce identical execution plans in both cases.

Obviously, the two syntax styles are not equivalent for outer joins, for those you have to use the join ... on syntax

Regarding your question,

It is the same both 'on' or 'where' on an inner join as long as your server can get it:

select * from a inner join b on a.c = b.c

and

select * from a inner join b where a.c = b.c

The 'where' option not all interpreters know so maybe should be avoided. And of course the 'on' clause is clearer.

a. WHERE clause: After joining, Records will be filtered.

b. ON clause - Before joining, Records (from right table) will be filtered.

To add onto Joel Coehoorn's response, I'll add some sqlite-specific optimization info (other SQL flavors may behave differently). In the original example, the LEFT JOINs have a different outcome depending on whether you use JOIN ON ... WHERE or JOIN ON ... AND. Here is a slightly modified example to illustrate:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
    WHERE Orders.Username = OrderLines.Username

versus

SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID 
    AND Orders.Username = OrderLines.Username

Now, the original answer states that if you use a plain inner join instead of a left join, the outcome of both queries will be the same, but the execution plan will differ. I recently realized that the semantic difference between the two is that the former forces the query optimizer to use the index associated with the ON clause, while the latter allows the optimizer to choose any index within the ON ... AND clauses, depending on what it thinks will work best.

Occasionally, the optimizer will guess wrong and you'll want to force a certain execution plan. In this case, let's say that the SQLite optimizer wrongly concludes that the fastest way to perform this join would be to use the index on Orders.Username, when you know from empirical testing that the index on Orders.ID would deliver your query faster.

In this case, the former JOIN ON ... WHERE syntax essentially allows you to force the primary join operation to occur on the ID parameter, with secondary filtering on Username performed only after the main join is complete. In contrast, the JOIN ON ... AND syntax allows the optimizer to pick whether to use the index on Orders.ID or Orders.Username, and there is the theoretical possibility that it picks the one that ends up slower.