The difference between inner join, left join and right join in mysql

The difference between inner join, left join and right join in MySQL is: inner join only returns matching rows in both tables, while left join returns all rows of the left table, including matching right table rows, right The join returns all rows from the right table, including matching rows from the left table. Inner join syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2; Left join syntax: SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2; Right join syntax: SELE

The difference between inner join, left join and right join in MySQL

INNER JOIN(INNER JOIN)

• Return only records with matching rows in both tables.
• Records that fail to match will be discarded.

LEFT JOIN

• Returns all records in the left table, even if there are no matching rows in the right table.
• Records in the right table that do not have matching rows will be filled with NULL values.

RIGHT JOIN

• Returns all records in the right table, even if there are no matching rows in the left table.
• Records in the left table that do not have matching rows will be filled with NULL values.

Usage

• Inner join: Used to find records with matching rows between two tables and discard them Unmatched records.
• Left join: Used to find all records in the left table and include records of matching rows in the right table.
• Right join: Used to find all records in the right table and include records of matching rows in the left table.

Syntax

• Inner join:

  <code class="sql">SELECT *
  FROM table1
  INNER JOIN table2
  ON table1.column1 = table2.column2;</code>

• Left join:

  <code class="sql">SELECT *
  FROM table1
  LEFT JOIN table2
  ON table1.column1 = table2.column2;</code>

• Right join:

  <code class="sql">SELECT *
  FROM table1
  RIGHT JOIN table2
  ON table1.column1 = table2.column2;</code>

Example

Suppose we have the following two tables:

<code>Table1:
| id | name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Bob |

Table2:
| id | address |
|---|---|
| 1 | 123 Main St |
| 2 | 456 Elm St |
| 4 | 789 Oak St |</code>

• Inner join:

  <code class="sql">SELECT *
  FROM Table1
  INNER JOIN Table2
  ON Table1.id = Table2.id;</code>

  Result:

id	name	address
1	John	123 Main St
2	Mary	456 Elm St

• Left join:

  <code class="sql">SELECT *
  FROM Table1
  LEFT JOIN Table2
  ON Table1.id = Table2.id;</code>

  Result:

##idnameaddress##123

John	123 Main St
Mary	456 Elm St
Bob	NULL

##Right join:

• <code class="sql">SELECT *
  FROM Table1
  RIGHT JOIN Table2
  ON Table1.id = Table2.id;</code>

  Result :

idnameaddress1John123 Main St2Mary456 Elm St4NULL789 Oak St

The above is the detailed content of The difference between inner join, left join and right join in mysql. For more information, please follow other related articles on the PHP Chinese website!