MySQL Inner Join VS Left Join w / IS NOT NULL?

Question

Is there any difference in performance in the following?

SELECT person.id
FROM person
LEFT JOIN address ON person.id = address.personID
WHERE address.personID IS NOT NULL

vs

SELECT person.id
FROM person
INNER JOIN address ON person.id = address.personID

This request should display the identifier of a user who has an address record (not all). The logical goal here would be to use Inner Join, as the second example shows. For reasons that are not entirely important (the request is created from the query builder), I may have to use the first approach.

Curious what the effect is. Does MySQL make a lot of unnecessary work when it has a LEFT JOIN, and then compares this field to zero to reduce the set? Maybe how INNER JOIN works behind the scenes?

Answer 1

MySQL, . , , person, address, .

@spencer7593 , , , . ( " " , .)

, , , WHERE . , , , .

Answer 2

, :

SELECT p.*, s.*
  FROM p
  LEFT
  JOIN s ON s.col = p.col
 WHERE s.col IS NOT NULL

SELECT p.*, s.*
  FROM p
 INNER
  JOIN s ON s.col = p.col

id select_type table  type poss key  key_len ref   rows Extra
-- ----------- ------ ---- ---- ---- ------- ----- ---- --------
 1 SIMPLE      p      ALL  -    -    -       -        3
 1 SIMPLE      s      ref  s_ix s_ix 9       p.col    1

id select_type table  type poss key  key_len ref   rows Extra
-- ----------- ------ ---- ---- ---- ------- ----- ---- -----------------------------
 1 SIMPLE      s      ALL  s_ix -    -       -        2
 1 SIMPLE      p      ALL  p_ix -    -       -        3 Using where; Using join buffer

, , . . , ; , INNER JOIN . , , LEFT JOIN, .

Answer 3

LEFT JOIN , INNER JOIN, LEFT JOIN , INNER JOIN, , (.. null ). , , INNER JOIN... INNER JOIN