CODEWITHSUNDEEP
mysqljoin
cerd
cerd

Reputation: 2231

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

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?

Upvotes: 46

Views: 67387

Answers (5)

philf2b
philf2b

Reputation: 201

An INNER JOIN will only return the rows where there are matching values in both tables, whereas a LEFT JOIN will return ALL the rows from the LEFT table even if there is no matching row in the RIGHT table.

TableA

ID   Value
1    TableA.Value1
2    TableA.Value2
3    TableA.Value3

TableB

ID   Value
2    TableB.ValueB
3    TableB.ValueC

An INNER JOIN produces:

SELECT a.ID,a.Value,b.ID,b.Value 
FROM TableA a INNER JOIN TableB b ON b.ID = a.ID

a.ID    a.Value            b.ID    b.Value
2       TableA.Value2      2       TableB.ValueB
3       TableA.Value3      3       TableB.ValueC

A LEFT JOIN produces:

SELECT a.ID,a.Value,b.ID,b.Value 
FROM TableA a LEFT JOIN TableB b ON b.ID = a.ID

a.ID    a.Value            b.ID    b.Value
1       TableA.Value1      NULL    NULL
2       TableA.Value2      2       TableB.ValueB
3       TableA.Value3      3       TableB.ValueC

The LEFT JOIN includes the row from TableA where ID = 1 even though there's no matching row in TableB where ID = 1, whereas the INNER JOIN excludes the row specifically because there's no matching row in TableB.

Upvotes: 8

rob
rob

Reputation: 2123

Inner joins narrow down the set of rows returns. Outer joins (left or right) don't change number of rows returned, but just "pick up" additional columns if possible.

In your first example, the result will be rows from AECounts that match the conditions specified to the 1_low_level_term table. Then for those rows, it tries to join to 1_pref_term and 1_soc_term. But if there's no match, the rows remain and the joined in columns are null.

Upvotes: 13

Scott
Scott

Reputation: 2760

An INNER JOIN is always a subset of a LEFT or RIGHT JOIN, and all of these are always subsets of a FULL JOIN.

Upvotes: 4

Seung Bae Im
Seung Bae Im

Reputation: 61

Use an inner join when you want only the results that appear in both tables that matches the Join condition.

Use a left join when you want all the results from Table A, but if Table B has data relevant to some of Table A's records, then you also want to use that data in the same query.

Use a full join when you want all the results from both Tables.

Upvotes: 6

Matt Fenwick
Matt Fenwick

Reputation: 49085

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.

Upvotes: 140

Related Questions