After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different.
What is the difference and what should go in each?
If I understand the theory correctly, the query optimizer should be able to use both interchangeably.
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.
Does not matter for inner joins
Matters for outer joins
a. WHERE clause: After joining. Records will be filtered after join has taken place.
b. ON clause - Before joining. Records (from right table) will be filtered before joining. This may end up as null in the result (since OUTER join).
Example: Consider the below tables:
1. documents:
| id | name |
--------|-------------|
| 1 | Document1 |
| 2 | Document2 |
| 3 | Document3 |
| 4 | Document4 |
| 5 | Document5 |
2. downloads:
| id | document_id | username |
|------|---------------|----------|
| 1 | 1 | sandeep |
| 2 | 1 | simi |
| 3 | 2 | sandeep |
| 4 | 2 | reya |
| 5 | 3 | simi |
a) Inside WHERE clause:
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
WHERE username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 1 | Document1 | 2 | 1 | simi |
| 2 | Document2 | 3 | 2 | sandeep |
| 2 | Document2 | 4 | 2 | reya |
| 3 | Document3 | 5 | 3 | simi |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
After applying the `WHERE` clause and selecting the listed attributes, the result will be:
| name | id |
|--------------|----|
| Document1 | 1 |
| Document2 | 3 |
b) Inside JOIN clause
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
AND username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 2 | Document2 | 3 | 2 | sandeep |
| 3 | Document3 | NULL | NULL | NULL |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.
After Selecting the listed attributes, the result will be:
| name | id |
|------------|------|
| Document1 | 1 |
| Document2 | 3 |
| Document3 | NULL |
| Document4 | NULL |
| Document5 | NULL |
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.
Considering we have the following post and post_comment tables:
The post has the following records:
| id | title |
|----|-----------|
| 1 | Java |
| 2 | Hibernate |
| 3 | JPA |
and the post_comment has the following three rows:
| id | review | post_id |
|----|-----------|---------|
| 1 | Good | 1 |
| 2 | Excellent | 1 |
| 3 | Awesome | 2 |
The SQL JOIN clause allows you to associate rows that belong to different tables. For instance, a CROSS JOIN will create a Cartesian Product containing all possible combinations of rows between the two joining tables.
While the CROSS JOIN is useful in certain scenarios, most of the time, you want to join tables based on a specific condition. And, that's where INNER JOIN comes into play.
The SQL INNER JOIN allows us to filter the Cartesian Product of joining two tables based on a condition that is specified via the ON clause.
If you provide an "always true" condition, the INNER JOIN will not filter the joined records, and the result set will contain the Cartesian Product of the two joining tables.
For instance, if we execute the following SQL INNER JOIN query:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1
We will get all combinations of post and post_comment records:
| p.id | pc.id |
|---------|------------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
So, if the ON clause condition is "always true", the INNER JOIN is simply equivalent to a CROSS JOIN query:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id
On the other hand, if the ON clause condition is "always false", then all the joined records are going to be filtered out and the result set will be empty.
So, if we execute the following SQL INNER JOIN query:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id
We won't get any result back:
| p.id | pc.id |
|---------|------------|
That's because the query above is equivalent to the following CROSS JOIN query:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id
The most common ON clause condition is the one that matches the Foreign Key column in the child table with the Primary Key column in the parent table, as illustrated by the following query:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id
When executing the above SQL INNER JOIN query, we get the following result set:
| p.id | pc.post_id | pc.id | p.title | pc.review |
|---------|------------|------------|------------|-----------|
| 1 | 1 | 1 | Java | Good |
| 1 | 1 | 2 | Java | Excellent |
| 2 | 2 | 3 | Hibernate | Awesome |
So, only the records that match the ON clause condition are included in the query result set. In our case, the result set contains all the post along with their post_comment records. The post rows that have no associated post_comment are excluded since they can not satisfy the ON Clause condition.
Again, the above SQL INNER JOIN query is equivalent to the following CROSS JOIN query:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id
The non-struck rows are the ones that satisfy the WHERE clause, and only these records are going to be included in the result set. That's the best way to visualize how the INNER JOIN clause works.
| p.id | pc.post_id | pc.id | p.title | pc.review | |------|------------|-------|-----------|-----------| | 1 | 1 | 1 | Java | Good | | 1 | 1 | 2 | Java | Excellent || 1 | 2 | 3 | Java | Awesome || 2 | 1 | 1 | Hibernate | Good || 2 | 1 | 2 | Hibernate | Excellent || 2 | 2 | 3 | Hibernate | Awesome || 3 | 1 | 1 | JPA | Good || 3 | 1 | 2 | JPA | Excellent || 3 | 2 | 3 | JPA | Awesome |
An INNER JOIN statement can be rewritten as a CROSS JOIN with a WHERE clause matching the same condition you used in the ON clause of the INNER JOIN query.
Not that this only applies to INNER JOIN, not for OUTER JOIN.
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
There is great difference between where clause vs. on clause, when it comes to left join.
Here is example:
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| fid | int(11) | NO | | NULL | |
| v | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
There fid is id of table t2.
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| v | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Query on "on clause" :
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K'
-> ;
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 1 | 1 | H | NULL | NULL |
| 2 | 1 | B | NULL | NULL |
| 3 | 2 | H | NULL | NULL |
| 4 | 7 | K | NULL | NULL |
| 5 | 5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)
Query on "where clause":
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 4 | 7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)
It is clear that, the first query returns a record from t1 and its dependent row from t2, if any, for row t1.v = 'K'.
The second query returns rows from t1, but only for t1.v = 'K' will have any associated row with it.
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.
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
In SQL, the 'WHERE' and 'ON' clause,are kind of Conditional Statemants, but the major difference between them are, the 'Where' Clause is used in Select/Update Statements for specifying the Conditions, whereas the 'ON' Clause is used in Joins, where it verifies or checks if the Records are Matched in the target and source tables, before the Tables are Joined
For Example: - 'WHERE'
SELECT * FROM employee WHERE employee_id=101
For Example: - 'ON'
There are two tables employee and employee_details, the matching columns are employee_id.
SELECT * FROM employee
INNER JOIN employee_details
ON employee.employee_id = employee_details.employee_id
Hope I have answered your Question. Revert for any clarifications.
For an inner join, WHERE and ON can be used interchangeably. In fact, it's possible to use ON in a correlated subquery. For example:
update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)
This is (IMHO) utterly confusing to a human, and it's very easy to forget to link table1 to anything (because the "driver" table doesn't have an "on" clause), but it's legal.
for better performance tables should have a special indexed column to use for JOINS .
so if the column you condition on is not one of those indexed columns then i suspect it is better to keep it in WHERE .
so you JOIN using the indexed columns, then after JOIN you run the condition on the none indexed column .
Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It’s possible, though that you might want to filter one or both of the tables before joining them. i.e, the where clause applies to the whole result set whereas the on clause only applies to the join in question.
I think this distinction can best be explained via the logical order of operations in SQL, which is, simplified:
FROM (including joins)WHEREGROUP BYHAVINGWINDOWSELECTDISTINCTUNION, INTERSECT, EXCEPTORDER BYOFFSETFETCHJoins 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
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
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 ... onsyntax
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.
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.
