SQL JOIN - WHERE clause vs ON clause

Question

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

User · Answer

this is my solution   SELECT song ID songs fullname  singers fullname FROM music JOIN songs ON songs ID   music song ID   JOIN singers ON singers ID   music singer ID GROUP BY songs fullname   You must have the GROUP BY to get it to work   Hope this help

User · Answer

There is great difference between where clause vs  on clause  when it comes to left join    Here is example   mysql gt  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 gt  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 gt  SELECT   FROM  t1  left join t2 on fid   t2 id AND t1 v    K       - gt     ---- ----- --- ------ ------    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 gt  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

User · Answer

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  lt  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  lt  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  lt  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

User · Answer

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

User · Answer

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  lt  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  lt  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  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I think it s the join sequence effect   In the upper left join case  SQL do Left join first and then do where filter  In the downer case  find Orders ID 12345 first  and then do join

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

this is my solution   SELECT song ID songs fullname  singers fullname FROM music JOIN songs ON songs ID   music song ID   JOIN singers ON singers ID   music singer ID GROUP BY songs fullname   You must have the GROUP BY to get it to work   Hope this help

User · Answer

I think it s the join sequence effect   In the upper left join case  SQL do Left join first and then do where filter  In the downer case  find Orders ID 12345 first  and then do join

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is great difference between where clause vs  on clause  when it comes to left join    Here is example   mysql gt  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 gt  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 gt  SELECT   FROM  t1  left join t2 on fid   t2 id AND t1 v    K       - gt     ---- ----- --- ------ ------    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 gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Table relationship 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          SQL INNER JOIN 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  SQL INNER JOIN - ON  quot always true quot  condition If you provide an  quot always true quot  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  quot p id quot      pc id AS  quot pc id quot  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  quot always true quot   the INNER JOIN is simply equivalent to a CROSS JOIN query  SELECT    p id AS  quot p id quot      pc id AS  quot pc id quot  FROM post p CROSS JOIN post comment WHERE 1   1 ORDER BY p id  pc id  SQL INNER JOIN - ON  quot always false quot  condition On the other hand  if the ON clause condition is  quot always false quot   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  quot p id quot      pc id AS  quot pc id quot  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  quot p id quot      pc id AS  quot pc id quot  FROM post p CROSS JOIN post comment WHERE 1   0 ORDER BY p id  pc id  SQL INNER JOIN - ON clause using the Foreign Key and Primary Key columns 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  quot p id quot      pc post id AS  quot pc post id quot      pc id AS  quot pc id quot      p title AS  quot p title quot      pc review  AS  quot pc review quot  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  quot p id quot      pc post id AS  quot pc post id quot      pc id AS  quot pc id quot      p title AS  quot p title quot      pc review  AS  quot pc review quot  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      Conclusion 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

User · Answer

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

User · Answer

Table relationship 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          SQL INNER JOIN 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  SQL INNER JOIN - ON  quot always true quot  condition If you provide an  quot always true quot  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  quot p id quot      pc id AS  quot pc id quot  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  quot always true quot   the INNER JOIN is simply equivalent to a CROSS JOIN query  SELECT    p id AS  quot p id quot      pc id AS  quot pc id quot  FROM post p CROSS JOIN post comment WHERE 1   1 ORDER BY p id  pc id  SQL INNER JOIN - ON  quot always false quot  condition On the other hand  if the ON clause condition is  quot always false quot   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  quot p id quot      pc id AS  quot pc id quot  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  quot p id quot      pc id AS  quot pc id quot  FROM post p CROSS JOIN post comment WHERE 1   0 ORDER BY p id  pc id  SQL INNER JOIN - ON clause using the Foreign Key and Primary Key columns 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  quot p id quot      pc post id AS  quot pc post id quot      pc id AS  quot pc id quot      p title AS  quot p title quot      pc review  AS  quot pc review quot  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  quot p id quot      pc post id AS  quot pc post id quot      pc id AS  quot pc id quot      p title AS  quot p title quot      pc review  AS  quot pc review quot  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      Conclusion 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

User · Answer

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

User · Answer

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

[sql] SQL JOIN - WHERE clause vs. ON clause

Examples related to sql

Examples related to join

Examples related to where-clause

Examples related to on-clause