By JCCyC


2009-06-19 16:16:24 8 Comments

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?

11 comments

@Kviz Majster 2019-07-09 16:23:52

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 same query plan so there is definitely no obvious difference in classic queries.

@Quassnoi 2009-06-19 16:17:28

INNER JOIN is ANSI syntax which 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.

@allyourcode 2009-07-25 21:00:40

Thanks, Quassnoi. You've got alot of details in your ans; is it fair to say that "yes, those queries are equivalent, but you should use inner join because it's more readable, and easier to modify"?

@Quassnoi 2009-07-26 11:49:28

@allyourcode: for Oracle, SQL Server, MySQL and PostgreSQL — yes. For other systems, probably, too, but you better check.

@Bill Karwin 2010-01-13 22:41:22

FWIW, using commas with join conditions in the WHERE clause is also in the ANSI standard.

@Quassnoi 2010-01-13 23:00:26

@Bill Karwin: JOIN keyword was not a part of proprietary standards until the past more recent that it may seem. It made its way into Oracle only in version 9 and into PostgreSQL in version 7.2 (both released in 2001). Appearance of this keyword was a part of ANSI standard adoption, and that's why this keyword is usually associated with ANSI, despite the fact the latter supports comma as a synonym for CROSS JOIN as well.

@Bill Karwin 2010-01-13 23:03:25

Nevertheless, ANSI SQL-89 specified joins to be done with commas and conditions in a WHERE clause (without conditions, a join is equivalent to a cross join, as you said). ANSI SQL-92 added the JOIN keyword and related syntax, but comma-style syntax is still supported for backward compatiblity.

@Bill Karwin 2010-01-13 23:04:27

InterBase 4.0 is an example of a commercial RDBMS implementation that supported JOIN syntax as early as 1994.

@BlackTigerX 2013-09-04 00:10:34

Just a note to be clear, implicit vs explicit joins are NOT the same, implicit joins will surprise you every once in a while when "nothing changed", especifically when dealing with null values (bugs in production); if you want to joins tables, do so explicitly (join... on...) and avoid your self the headache

@Quassnoi 2013-09-04 18:03:13

@BlackTigerX: could you please be more specific about implicit and explicit joins "being not the same"?

@BlackTigerX 2013-09-12 21:03:37

Again, it's when working with null values, usually with joins on multiple columns, and some of those values are null, you will get different results (e.g.: empty vs not empty) depending on which join you use

@Arvind Sridharan 2013-10-16 08:16:27

is there a performance gain by using join on instead of using WHERE?

@Quassnoi 2013-10-16 10:19:52

@ArvindSridharan: no

@philipxy 2015-11-26 22:20:10

Implicit join (,) is exactly the same as CROSS JOIN and (INNER) JOIN ON 1=1 except it has lower precedence. (OUTER) LEFT/RIGHT/FULL JOINs differ from (INNER) JOIN (they can add extra rows with NULLs). You do have to use ON in JOINs before OUTER JOINs if you don't use a subselect with WHERE instead. (@BlackTigerX is wrong.)

@PlexQ 2017-08-03 21:26:39

These are NOT synonyms in MySQL, MySQL's optimizer will pick your join order if you supply explicitly versus optimized order for earlier MySQL version

@Quassnoi 2017-08-03 22:15:27

@plexq: are you saying the two queries from the op would yield different plans?

@rafidheen 2009-12-22 06:24:07

Applying conditional statements in ON / WHERE

Here I have explained about 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 <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

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.

  1. 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.

  2. ON: The ON filter is applied to VT1. Only rows for which the <join_condition> is TRUE are inserted to VT2.

  3. 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.

  4. WHERE: The WHERE filter is applied to VT3. Only rows for which the <where_condition> is TRUE are inserted to VT4.

  5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.

  6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.

  7. HAVING: The HAVING filter is applied to VT6. Only groups for which the <having_condition> is TRUE are inserted to VT7.

  8. SELECT: The SELECT list is processed, generating VT8.

  9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.

  10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).

  11. 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 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 how many tables you have joined and number of rows available in each join tables)

@Mike Sherrill 'Cat Recall' 2013-07-04 00:58:44

"Therefore, (INNER JOIN) ON will filter the data (The data count of VT will be reduced here itself) before applying WHERE clause." Not necessarily. The article is about the logical order of processing. When you say a particular implementation will do one thing before another thing, you're talking about the implemented order of processing. Implementations are allowed to make any optimizations they like, as long as the result is the same as if the implementation followed the logical order. Joe Celko has written a lot about this on Usenet.

@James 2018-01-30 04:09:26

@rafidheen "(INNER JOIN) ON will filter the data ... before applying WHERE clause ... which improves performance." Good point. "After that only the WHERE condition will apply filter conditions" What about the HAVING clause?

@philipxy 2018-06-24 06:17:56

@James That claim by rafidheen is wrong. See 'join optimization' in the manual. Also my other comments on this page. (And MikeSherrill'CatRecall''s.) Such "logical" descriptions describe the result value, not how it is actually calculated. And such implementation behaviour is not guaranteed to not change.

@HLGEM 2009-06-19 16:46:59

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 syntax that has been outdated for 20 years? Why do you want to write database code that is?

@Quassnoi 2009-06-19 21:37:02

@HLGEM: While I agree completely that explicit JOINs are better, there are cases when you just need to use the old syntax. A real world example: ANSI JOIN's got into Oracle only in version 9i which was released in 2001, and until only a year ago (16 years from the moment the standard was published) I had to support a bunch of 8i installation for which we had to release critical updates. I didn't want to maintain two sets of updates, so we developed and tested the updates against all databases including 8i, which meant we were unable to use ANSI JOINs.

@Marco Demaio 2011-02-11 18:56:32

+1 interesting point when you point out that the sintax without INNER JOIN is more error prone. I'm confused about your last sentence when you say "...the standard using the explicit joins is 17 years old." so are you then suggesting to use the INNER JOIN keyword or not?

@HLGEM 2011-02-11 18:59:31

@Marco Demaio, yes always use INNER JOIN or JOIN (these two are the same)or LEFT JOIN or RIGHT JOIN or CROSS JOIN and never use the implicit comma joins.

@onedaywhen 2016-10-14 13:45:00

"Why do you want to write database code that is [20 years old]?" - I notice you write SQL using HAVING which has been 'outdated' since SQL started supporting derived tables. I also notice you don't use NATURAL JOIN even though I would argue it has made INNER JOIN 'outdated'. Yes, you have your reasons (no need to state them again here!): my point is, those who like using the older syntax have their reasons too and the relative age of the syntax is of little if any relevance.

@HLGEM 2016-10-14 13:58:41

Natural Joins are not supported in the database I use. I don't understand why HAVING is replaced by derived tables, please explain, I really would be interested. However, I have never seen anyone who uses implicit joins give a reason to use them that isn't,"It is what I am used to." If you have one, I would like to hear it. There is no technical advantage that I know of for an implicit join over an inner join. There are technical disadvantages because implicit joins can lead to bad result sets due to unidentified accidental cross joins which are not possible using the inner join syntax.

@HLGEM 2016-10-18 13:30:20

@onedaywhen, It is especially critical that people new to SQL avoid implicit joins, they are the very people who get wrong results because they don't understand join concepts. And you are still writing the join conditions in the where clause so you save nothing except the time it takes to write the word join. Given that you write the conditions later in the query, it probably actually takes less time thanjumping up and down in the query to add the where clause as you add the join. The I'm too tired argument is silly. You can write joins as easily or more easily than an implicit join.

@onedaywhen 2016-10-18 14:53:56

Wasn't looking for an argument, genuinely though you wanted info from me. But I maintain that "outdated for 20 years" is a poor defense and stand by my original comments.

@Jürgen A. Erhard 2017-02-23 03:52:45

WHERE is still in the standard (show me where it's not). So, nothing outdated, apparently. Also, "rather than fixing the join" shows me a developer who should be kept away from DBMSs in general, far away.

@Tom 2018-04-27 03:35:32

onedaywhen: "relative age of the syntax" is of "relevance". Usu., a newer syntax / feature is meant to be more efficient and/or readable (in this case mainly the latter but also, as @HLGEM mentioned, even the former as far as typing due less jumping around), than older syntax that's functionally equivalent.

@Tom 2018-04-27 03:35:51

@Jürgen A. Erhard: The fact that a syntax (/ feature) "is still in the standard" (/supported) is a poor defense for using it. There are countless examples of where an older syntax (/ feature) "is still in the standard" (/ supported) mainly, if not only, for backwards compatibility. Plus, HLGEM never said that the WHERE form is no longer in the standard, merely that the JOIN form (mostly likely and by most accounts meant to be "'better'") has been around for plenty of time to significantly reduce, if not, eliminate reasons (i.e. to work with legacy code) to use the older form.

@John Gietzen 2009-06-19 16:20:49

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.

@Brent Baisley 2009-06-19 17:28:56

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.

@philipxy 2018-04-21 04:43:11

Standard SQL didn't change. MySQL was just wrong & now is right. See the MySQL manual.

@João Marcus 2009-06-19 17:03:42

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.

@Benzo 2009-06-19 16:50:12

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 compatability mode) for 2005 sql server and later.

@HLGEM 2009-06-19 18:36:49

Even in SQL Server 2000, = and = could give wrong results and should never be used.

@philipxy 2015-11-26 22:43:40

*= and =* were never ANSI and were never a good notation. That's why ON was needed--for OUTER JOINs in the absence of subselects (which got added at the same time, so they aren't actually needed in CROSS & INNER JOINs.)

@Carl Manaster 2009-06-19 16:30:59

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.

@allyourcode 2009-07-25 21:08:08

Thanks for clarifying why inner join is preferred Carl. I think your ans was implicit in the others, but explicit is usually better (yes, I'm a Python fan).

@philipxy 2015-11-26 22:33:40

The semantics of ON and WHERE mean that for JOINs after the last OUTER JOIN it doesn't matter which you use. Although you characterize ON as part of the JOIN, it is also a filtering after a Cartesian product. Both ON and WHERE filter a Cartesian product. But either ON or a subselect with WHERE must be used before the last OUTER JOIN. (JOINs aren't "on" column pairs. Any two tables can be JOINed ON any condition. That's just a way to interpret JOINs ON equality of columns specifically.)

@cybergeek654 2019-07-16 17:14:56

Even when you are using WHERE to the same effect of INNER JOIN, you are going to mention your two tables in the FROM part of the query. So basically you are still implying where you are getting your data in the FROM clause, so I guess you cannot say it necessarily "conflates the which and the where-from"

@Cade Roux 2009-06-19 16:23:45

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 rearrranged by the optimizer.

I recommend you write the queries in the most readble way possible.

Sometimes this includes making the INNER JOIN relatively "incomplete" 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.

@allyourcode 2009-07-25 21:10:17

Your first snippet definitely hurts my brain more. Does anyone actually do that? If I meet someone that does that, is it ok for me to beat him over the head?

@Cade Roux 2009-07-26 01:25:49

I locate the criteria where it makes the most sense. If I'm joining to a temporally consistent snapshot lookup table (and I don't have a view or UDF which enforces the selection of a valid date), I will include the effective date in the join and not in the WHERE because it's less likely to accidentally get removed.

@Dave Markle 2011-11-10 20:33:37

@allyourcode: though it's rare to see this type of join syntax in INNER JOINs, it's quite common for RIGHT JOINs and LEFT JOINS -- specifying more detail in the join predicate eliminates the need for a subquery and prevents your outer joins from inadvertently being turned into INNER JOINs. (Though I agree that for INNER JOINs I'd almost always put c.State = 'NY' in the WHERE clause)

@Arth 2014-08-12 19:49:55

@allyourcode I definitely do that! And I agree with Cade.. I'm curious as to whether there is a decent reason not to

@matt b 2009-06-19 16:19:14

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.

@Noah Yetter 2009-06-19 20:40:21

I couldn't disagree more. JOIN syntax is extremely wordy and difficult to organize. I have plenty of queries joining 5, 10, even 15 tables using WHERE clause joins and they are perfectly readable. Rewriting such a query using a JOIN syntax results in a garbled mess. Which just goes to show there is no right answer to this question and that it depends more on what you're comfortable with.

@matt b 2009-06-21 19:42:08

Noah, I think you might be in the minority here.

@allyourcode 2009-07-25 21:05:40

I get +1 to matt and Noah. I like diversity :). I can see where Noah is coming from; inner join doesn't add anything new to the language, and is definitely more verbose. On the other hand, it can make your 'where' condition much shorter, which usually means it's easier to read.

@matt b 2009-07-27 12:45:54

I personally would go for readability over succinctness

@matt b 2009-09-14 14:53:42

I would assume that any sane DBMS would translate the two queries into the same execution plan; however in reality each DBMS is different and the only way to know for sure is to actually examine the execution plan (i.e., you'll have to test it yourself).

@James 2018-01-30 04:19:51

Is it true as @rafidheen suggested in another answer (the one with the detailed sequence of SQL execution) that JOINs are filtered one at a time, reducing the size of join opertations when compared to a full cartesian join of 3 or more tables, with the WHERE filter being applied retroactively? If so, it would suggest JOIN offers performance improvement (as well as advantages in left/right joins, as also pointed out on another answer).

@philipxy 2018-06-24 06:24:43

@James No, it's not true. Read about optimization in the manual & read textbooks re logical & physical relational query optimization.

Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

1 Answered Questions

[SOLVED] Using inner Join in Solr query

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

17 Answered Questions

[SOLVED] SQL join: where clause vs. on clause

18 Answered Questions

[SOLVED] Join vs. sub-query

  • 2010-04-05 06:22:22
  • Your Common Sense
  • 388433 View
  • 751 Score
  • 18 Answer
  • Tags:   sql mysql subquery join

3 Answered Questions

9 Answered Questions

[SOLVED] Python join: why is it string.join(list) instead of list.join(string)?

  • 2009-01-29 22:45:13
  • Evan Fosmark
  • 1215608 View
  • 1619 Score
  • 9 Answer
  • Tags:   python string list join

3 Answered Questions

Sponsored Content