Joining same table but different columns, is there a difference between these 2 approaches?-CodePudding

If you want to build a query that joins the same table but on different columns is there a a difference between these two approaches?

The first approach using two JOIN statements:

SELECT 
     * 
FROM 
     docs
JOIN authors on 
     docs.author_id = authors.id 
JOIN authors a2 ON 
     docs.id = a2.id

Versus the second approach, using one JOIN statement but combining it with the AND operator:

SELECT
     *
FROM
     docs
JOIN authors on 
     docs.id = authors.id
     AND docs.author_id = authors.id

Looking at the output here http://sqlfiddle.com/#!9/f7c9e0/11 it gives the same results if I'm not mistaken (the number of columns in the result differs of course but it gives back the same row). Why though? Are these queries exactly the same? And does this also apply when using the other join types like left join, right join etc?

CodePudding user response：

I believe those queries are actually very different.

In the first case you actually join three tables (the "authors" twice), so the resulting table is supposed to have double set of columns from the "author" table.

Here is an example of how it works with more records two joins

join by two conditions

CodePudding user response：

As say, the second query will yield additional columns but they will have an identical result in terms of rows.

What could vary is the performance. Instinctively I’d expect the single join on both columns to be faster, especially if you have an index on the two id columns on both tables.

What you might find interesting though is if you put the keyword EXPLAIN before each select you can see a description of the query plan for each query which will show you how MySQL is going about retrieving the data. It’s even possible that the engine is clever enough to optimise the first query to be more like the second.