4

Given the query below

    SELECT * FROM tableA 
    LEFT JOIN tableB ON tableB.id = tableA.id
    WHERE tableA.name = 'e'

.

tableA                        tableB
-----------                   ----------- 
id    name                     id    school
-----------                   -----------  
1     a                         1     AA
2     b                         2     BB                     
3     c                         3     CC                     
4     d                         4     DD                     
5     e                         5     EE  

.

What baffles me is the process that is happening behind.

Does the system JOINs first both tableA and tableB before they select only the row from the WHERE clause?

If yes, then what about this query

SELECT * FROM 
(
    SELECT * FROM tableA     
    WHERE name = 'e'

) A LEFT JOIN tableB ON tableB.id = A.id 

Does this query select first using the WHERE clause before it JOINs the other table?

Sorry I can't find the answer on the web. I wanted to lessen the MySQL resource bandwidth. Does the second query is the best for this situation?

  • 1
    You can use explain to see what MySQL does. And yes, in your case, it will first look in table A. Preferable with an index. Your second query should by identical to your first one, although in older mysql versions, it will create an additional derived table (which is slower). – Solarflare Feb 27 '17 at 15:51
  • @Solarflare the problem is that I can't understand the EXPLAIN thing. But since you said that the DERIVED table is slower, you gave me some knowledge. – Jam Ville Feb 27 '17 at 16:21
  • 1
    Show us the EXPLAIN, we will help explain how it applies. (I agree that EXPLAIN is cryptic.) – Rick James Feb 27 '17 at 21:35
5

The query optimizer will decide and it is pretty smart.

SELECT * FROM tableA 
LEFT JOIN tableB 
  ON tableB.id = tableA.id
WHERE tableA.name = 'e'  

There are many cases where doing the join first is more efficient. If name is indexed and 'e' is somewhat unique then do that first is more efficient.

This is different

SELECT * FROM tableA 
LEFT JOIN tableB 
  ON tableB.id = tableA.id
WHERE tableB.school = 'EE'   

That changes the left join into an inner join but since there is a match in your data you would get the same results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.