When to use LEFT JOIN and when to use INNER JOIN?

Question

I feel like I was always taught to use LEFT JOINs and I often see them mixed with INNERs to accomplish the same type of query throughout several pieces of code that are supposed to do the same thing on different pages. Here goes:

SELECT ac.reac, pt.pt_name, soc.soc_name, pt.pt_soc_code
FROM
  AECounts ac
  INNER JOIN 1_low_level_term llt on ac.reac = llt.llt_name
  LEFT JOIN 1_pref_term pt ON llt.pt_code = pt.pt_code
  LEFT JOIN 1_soc_term soc ON pt.pt_soc_code = soc.soc_code
LIMIT 100,10000

Thats one I am working on:

I see a lot like:

SELECT COUNT(DISTINCT p.`case`) as count
FROM FDA_CaseReports cr
  INNER JOIN ae_indi i ON i.isr = cr.isr
  LEFT JOIN ae_case_profile p ON cr.isr = p.isr

This seems like the LEFT may as well be INNER is there any catch?

Answer 1

Is there any catch? Yes there is -- left joins are a form of outer join, while inner joins are a form of, well, inner join.

Here's examples that show the difference. We'll start with the base data:

mysql> select * from j1;
+----+------------+
| id | thing      |
+----+------------+
|  1 | hi         |
|  2 | hello      |
|  3 | guten tag  |
|  4 | ciao       |
|  5 | buongiorno |
+----+------------+

mysql> select * from j2;
+----+-----------+
| id | thing     |
+----+-----------+
|  1 | bye       |
|  3 | tschau    |
|  4 | au revoir |
|  6 | so long   |
|  7 | tschuessi |
+----+-----------+

And here we'll see the difference between an inner join and a left join:

mysql> select * from j1 inner join j2 on j1.id = j2.id;
+----+-----------+----+-----------+
| id | thing     | id | thing     |
+----+-----------+----+-----------+
|  1 | hi        |  1 | bye       |
|  3 | guten tag |  3 | tschau    |
|  4 | ciao      |  4 | au revoir |
+----+-----------+----+-----------+

Hmm, 3 rows.

mysql> select * from j1 left join j2 on j1.id = j2.id;
+----+------------+------+-----------+
| id | thing      | id   | thing     |
+----+------------+------+-----------+
|  1 | hi         |    1 | bye       |
|  2 | hello      | NULL | NULL      |
|  3 | guten tag  |    3 | tschau    |
|  4 | ciao       |    4 | au revoir |
|  5 | buongiorno | NULL | NULL      |
+----+------------+------+-----------+

Wow, 5 rows! What happened?

Outer joins such as left join preserve rows that don't match -- so rows with id 2 and 5 are preserved by the left join query. The remaining columns are filled in with NULL.

In other words, left and inner joins are not interchangeable.