The difference between on and where in mysql

The difference between ON and WHERE in MySQL: ON is used to connect tables and specify connection conditions; WHERE is used to filter the result set and select rows based on conditions. ON is used in the JOIN statement to connect multiple tables; WHERE is used in the SELECT statement to filter rows in the table. The execution order is ON before WHERE.

The difference between ON and WHERE in MySQL

In MySQL, ON and WHERE are both used to specify queries Conditional keywords, but they differ in usage and effect.

ON clause

• is used to connect the relationship between tables and specify the connection conditions between tables.
• Used in the JOIN statement to join two or more tables.
• Ensure matching rows are from the same row set.

WHERE clause

• is used to filter the result set and select the rows to be returned based on the specified conditions.
• Used in the SELECT statement to select qualified rows from the table.
• Can be applied to any column in any table.

Usage comparison

• ON clause：JOIN statement, used when joining tables .
• WHERE clause : Used when filtering the result set in the SELECT statement.

Execution order

During query execution, the ON clause is executed before the WHERE clause. This means that the ON clause is used first to join the tables, and then the WHERE clause is used to filter data from the joined result set.

Example

<code class="sql">-- ON 子句用于连接两个表
SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id;

-- WHERE 子句用于筛选结果集
SELECT * FROM customers WHERE age > 30;</code>

Summary

ON clause is used to join tables, while WHERE clause is used to filter the result set . They differ in usage and effect, and are performed in different orders.

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