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.