How to use on in mysql

The ON clause is used to specify the conditions for comparing and merging rows of two tables in JOIN operations, including inner joins, left outer joins, right outer joins and full outer joins. The ON condition uses only equality comparisons, you can use multiple conditions to specify more complex join rules, and can be used with other JOIN types.

Usage of ON in MySQL

Overview of ON clause

## The #ON clause is used to specify conditions for comparing and merging rows from two tables in a JOIN operation.

Syntax

<code>ON 表1.列名 = 表2.列名</code>

Usage

• INNER JOIN: Only Return matching rows from both tables.
• LEFT OUTER JOIN: Returns all rows in the left table, and any matching rows in the right table. If there is no match in the right table, NULL is returned.
• RIGHT OUTER JOIN: Similar to left outer join, but returns all rows in the right table.
• Full outer join (FULL OUTER JOIN): Returns all rows in the two tables, regardless of whether they match or not.

Example

Inner join:

<code>SELECT * FROM 表1
INNER JOIN 表2 ON 表1.id = 表2.id;</code>

Left outer join:

<code>SELECT * FROM 表1
LEFT OUTER JOIN 表2 ON 表1.id = 表2.id;</code>

Right outer join:

<code>SELECT * FROM 表1
RIGHT OUTER JOIN 表2 ON 表1.id = 表2.id;</code>

Full outer join:

<code>SELECT * FROM 表1
FULL OUTER JOIN 表2 ON 表1.id = 表2.id;</code>

Notes

ON conditions can only use equality comparison (=).
• You can use multiple ON conditions to specify more complex connection rules.
• The ON clause can be used with other JOIN types such as CROSS JOIN, NATURAL JOIN.

The above is the detailed content of How to use on in mysql. For more information, please follow other related articles on the PHP Chinese website!