How Do INNER, LEFT, RIGHT, and FULL Joins Differ in MySQL?

In -depth understanding of the differences in MySQL Join type

MySQL's Join clause allows you to combine the data in multiple tables by matching the public field value. According to different use cases, you can use a variety of Join types:

Inner Join (inner connection)

Inner Join returns only when there are two tables. It executes a collection of intersections, and retrieval is a record of two tables.

left join (left)

LEFT JOIN returns all the rows in the left table, regardless of whether there are matching in the right table. It uses the NULL value to fill in the matching items that are missing in the right table. Right Join (right connection)

Right Join is similar to LEFT JOIN, but it returns all lines in the right table instead of all lines in the left table. Similarly, the NULL value is used to represent the matching items that are missing in the left.

Full Join (full connection)

The results of the

Full Join combination of LEFT and Right are returned to all rows in the two tables. Any non -matching of any table will fill the NULL value.

Example

Consider two tables,

Tablea

and

Tableb , they include the following data:

Inner Join Example Result:

<code>**TableA**
| id | firstName | lastName |
|---|---|---|
| 1 | arun | prasanth |
| 2 | ann | antony |
| 3 | sruthy | abc |

**TableB**
| id2 | age | Place |
|---|---|---|
| 1 | 24 | kerala |
| 2 | 24 | usa |
| 3 | 25 | ekm |
| 5 | 24 | chennai |</code>

left join example

<code class="language-sql">SELECT *
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;</code>

Result:

firstName	lastName	age	Place
arun	prasanth	24	kerala
ann	antony	24	usa
sruthy	abc	25	ekm

Right Join Example

Result:

<code class="language-sql">SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;</code>

FULL JOIN example (MySQL does not support direct FULL JOIN and needs to be simulated using UNION)

MySQL does not directly support FULL JOIN. You need to use LEFT JOIN and RIGHT JOIN in combination with UNION to simulate the effect of FULL JOIN.

Through the above examples, you can clearly see the differences in data merging between different JOIN types. Choosing the right JOIN type is critical to efficiently getting the data you need.

The above is the detailed content of How Do INNER, LEFT, RIGHT, and FULL Joins Differ in MySQL?. For more information, please follow other related articles on the PHP Chinese website!