INNER JOIN ON vs WHERE clause

Question

For simplicity  assume all relevant fields are NOT NULL   You can do   SELECT     table1 this  table2 that  table2 somethingelse FROM     table1  table2 WHERE     table1 foreignkey   table2 primarykey     AND  some other conditions    Or else   SELECT     table1 this  table2 that  table2 somethingelse FROM     table1 INNER JOIN table2     ON table1 foreignkey   table2 primarykey WHERE      some other conditions    Do these two work on the same way in MySQL

User · Accepted Answer

INNER JOIN is ANSI syntax that you should use  It is generally considered more readable  especially when you join lots of tables  It can also be easily replaced with an OUTER JOIN whenever a need arises  The WHERE syntax is more relational model oriented  A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching  It s easier to see this with the WHERE syntax  As for your example  in MySQL  and in SQL generally  these two queries are synonyms  Also  note that MySQL also has a STRAIGHT JOIN clause  Using this clause  you can control the JOIN order  which table is scanned in the outer loop and which one is in the inner loop  You cannot control this in MySQL using WHERE syntax

User · Answer

The SQL 2003 standard changed some precedence rules so a JOIN statement takes precedence over a  comma  join  This can actually change the results of your query depending on how it is setup  This cause some problems for some people when MySQL 5 0 12 switched to adhering to the standard   So in your example  your queries would work the same  But if you added a third table  SELECT     FROM table1  table2 JOIN table3 ON     WHERE      Prior to MySQL 5 0 12  table1 and table2 would be joined first  then table3  Now  5 0 12 and on   table2 and table3 are joined first  then table1  It doesn t always change the results  but it can and you may not even realize it   I never use the  comma  syntax anymore  opting for your second example  It s a lot more readable anyway  the JOIN conditions are with the JOINs  not separated into a separate query section

User · Answer

I ll also point out that using the older syntax is more subject to error  If you use inner joins without an ON clause  you will get a syntax error  If you use the older syntax and forget one of the join conditions in the where clause  you will get a cross join  The developers often fix this by adding the distinct keyword  rather than fixing the join because they still don t realize the join itself is broken  which may appear to cure the problem but will slow down the query considerably  Additionally for maintenance if you have a cross join in the old syntax  how will the maintainer know if you meant to have one  there are situations where cross joins are needed  or if it was an accident that should be fixed  Let me point you to this question to see why the implicit syntax is bad if you use left joins  Sybase    to Ansi Standard with 2 different outer tables for same inner table Plus  personal rant here   the standard using the explicit joins is over 20 years old  which means implicit join syntax has been outdated for those 20 years  Would you write application code using a syntax that has been outdated for 20 years  Why do you want to write database code that is

User · Answer

They have a different human-readable meaning   However  depending on the query optimizer  they may have the same meaning to the machine   You should always code to be readable   That is to say  if this is a built-in relationship  use the explicit join   if you are matching on weakly related data  use the where clause

User · Answer

If you are often programming dynamic stored procedures  you will fall in love with your second example  using where   If you have various input parameters and lots of morph mess  then that is the only way  Otherwise  they both will run the same query plan so there is definitely no obvious difference in classic queries

User · Answer

Others have pointed out that INNER JOIN helps human readability  and that s a top priority  I agree  Let me try to explain why the join syntax is more readable   A basic SELECT query is this   SELECT stuff FROM tables WHERE conditions   The SELECT clause tells us what we re getting back  the FROM clause tells us where we re getting it from  and the WHERE clause tells us which ones we re getting   JOIN is a statement about the tables  how they are bound together  conceptually  actually  into a single table      Any query elements that control the tables - where we re getting stuff from - semantically belong to the FROM clause  and of course  that s where JOIN elements go    Putting joining-elements into the WHERE clause conflates the which and the where-from  that s why the JOIN syntax is preferred

User · Answer

ANSI join syntax is definitely more portable  I m going through an upgrade of Microsoft SQL Server  and I would also mention that the    and    syntax for outer joins in SQL Server is not supported  without compatibility mode  for 2005 SQL server and later

User · Answer

Implicit joins  which is what your first query is known as  become much much more confusing  hard to read  and hard to maintain once you need to start adding more tables to your query  Imagine doing that same query and type of join on four or five different tables     it s a nightmare   Using an explicit join  your second example  is much more readable and easy to maintain

User · Answer

I have two points for the implicit join  The second example    Tell the database what you want  not what it should do  You can write all tables in a clear list that is not cluttered by join conditions  Then you can much easier read what tables are all mentioned  The conditions come all in the WHERE part  where they are also all lined up one below the other  Using the JOIN keyword mixes up tables and conditions

User · Answer

The implicit join ANSI syntax is older  less obvious  and not recommended  In addition  the relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN  so even INNER JOIN queries with WHERE clauses can have the predicates rearranged by the optimizer  I recommend you write the queries in the most readable way possible  Sometimes this includes making the INNER JOIN relatively  quot incomplete quot  and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable  For example  instead of  SELECT   FROM Customers c INNER JOIN CustomerAccounts ca     ON ca CustomerID   c CustomerID     AND c State    NY  INNER JOIN Accounts a     ON ca AccountID   a AccountID     AND a Status   1  Write  SELECT   FROM Customers c INNER JOIN CustomerAccounts ca     ON ca CustomerID   c CustomerID INNER JOIN Accounts a     ON ca AccountID   a AccountID WHERE c State    NY      AND a Status   1  But it depends  of course

User · Answer

Applying conditional statements in ON   WHERE  Here I have explained the logical query processing steps   Reference  Inside Microsoft   SQL Server    2005 T-SQL Querying  Publisher  Microsoft Press  Pub Date  March 07  2006  Print ISBN-10  0-7356-2313-9  Print ISBN-13  978-0-7356-2313-2  Pages  640  Inside Microsoft   SQL Server    2005 T-SQL Querying  8   SELECT  9  DISTINCT  11  TOP  lt top specification gt   lt select list gt   1   FROM  lt left table gt   3         lt join type gt  JOIN  lt right table gt   2        ON  lt join condition gt   4   WHERE  lt where condition gt   5   GROUP BY  lt group by list gt   6   WITH  CUBE   ROLLUP   7   HAVING  lt having condition gt   10  ORDER BY  lt order by list gt   The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed  In most programming languages  the code is processed in the order in which it is written  In SQL  the first clause that is processed is the FROM clause  while the SELECT clause  which appears first  is processed almost last  Each step generates a virtual table that is used as the input to the following step  These virtual tables are not available to the caller  client application or outer query   Only the table generated by the final step is returned to the caller  If a certain clause is not specified in a query  the corresponding step is simply skipped  Brief Description of Logical Query Processing Phases Don t worry too much if the description of the steps doesn t seem to make much sense for now  These are provided as a reference  Sections that come after the scenario example will cover the steps in much more detail   FROM  A Cartesian product  cross join  is performed between the first two tables in the FROM clause  and as a result  virtual table VT1 is generated   ON  The ON filter is applied to VT1  Only rows for which the  lt join condition gt  is TRUE are inserted to VT2   OUTER  join   If an OUTER JOIN is specified  as opposed to a CROSS JOIN or an INNER JOIN   rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows  generating VT3  If more than two tables appear in the FROM clause  steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed   WHERE  The WHERE filter is applied to VT3  Only rows for which the  lt where condition gt  is TRUE are inserted to VT4   GROUP BY  The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause  VT5 is generated   CUBE   ROLLUP  Supergroups  groups of groups  are added to the rows from VT5  generating VT6   HAVING  The HAVING filter is applied to VT6  Only groups for which the  lt having condition gt  is TRUE are inserted to VT7   SELECT  The SELECT list is processed  generating VT8   DISTINCT  Duplicate rows are removed from VT8  VT9 is generated   ORDER BY  The rows from VT9 are sorted according to the column list specified in the ORDER BY clause  A cursor is generated  VC10    TOP  The specified number or percentage of rows is selected from the beginning of VC10  Table VT11 is generated and returned to the caller           Therefore   INNER JOIN  ON will filter the data  the data count of VT will be reduced here itself  before applying the WHERE clause   The subsequent join conditions will be executed with filtered data which improves performance  After that  only the WHERE condition will apply filter conditions    Applying conditional statements in ON   WHERE will not make much difference in few cases  This depends on how many tables you have joined and the number of rows available in each join tables

User · Answer

I know you re talking about MySQL  but anyway  In Oracle 9 explicit joins and implicit joins would generate different execution plans  AFAIK that has been solved in Oracle 10   there s no such difference anymore

[sql] INNER JOIN ON vs WHERE clause

Examples related to sql

Examples related to mysql

Examples related to join

Examples related to inner-join