找到你要的答案

Q:difference between efficiency these sql queries? [duplicate]

Q:区别这些SQL查询的效率?[重复]

This question already has an answer here:

I have 2 tables customer and order1. I want to know which of the following queries is more efficient

select cust_name,ISBN from customer,order1 where customer.cust_no=order1.cust_no;

,

select cust_name,ISBN from customer inner join order1 on customer.cust_no=order1.cust_no;

and

select cust_name,ISBN from customer natural join order1;

I've read that inner join takes cartesian product of two tables and then return only rows that match the 'on' condition. Does natural operates in the same way as inner join? Also how inline queries are efficient than joins?

这个问题在这里已经有了答案:

我有2张桌子和1客户。我想知道下面哪个查询更有效

select cust_name,ISBN from customer,order1 where customer.cust_no=order1.cust_no;

select cust_name,ISBN from customer inner join order1 on customer.cust_no=order1.cust_no;

select cust_name,ISBN from customer natural join order1;

I've read that inner join takes cartesian product of two tables 和 then return only rows that match the 'on' condition. Does natural operates in the same way as inner join? Also how inline queries are efficient than joins?

answer1: 回答1:

These three queries should do the same thing. You could verify by checking the execution plan, but any differences between them should be negligible.

These three queries should do the same thing. You could verify by checking the execution plan, but any differences between them should be negligible.

answer2: 回答2:

According to MySQL 5.7 Reference Manual:

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard.
These changes have five main aspects:
  • The way that MySQL determines the result columns of NATURAL or USING join operations (and thus the result of the entire FROM clause).
  • Expansion of SELECT * and SELECT tbl_name.* into a list of selected columns.
  • Resolution of column names in NATURAL or USING joins.
  • Transformation of NATURAL or USING joins into JOIN ... ON.
  • Resolution of column names in the ON condition of a JOIN ... ON.

Also, note that:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

And finally to answer your question regarding sub queries, from Rewriting Subqueries as Joins:

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types.

根据MySQL 5.7参考手册:

Natural joins 和 joins with USING, including outer join variants, are processed according to the SQL:2003 st和ard. The goal was to align the syntax 和 semantics of MySQL with respect to NATURAL JOINJOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the st和ard.
These changes have five main aspects:
  • The way that MySQL determines the result columns of NATURAL or USING join operations (和 thus the result of the entire FROM clause).
  • Expansion of SELECT *SELECT tbl_name.* into a list of selected columns.
  • Resolution of column names in NATURAL or USING joins.
  • Transformation of NATURAL or USING joins into JOIN ... ON.
  • Resolution of column names in the ON condition of a JOIN ... ON.

Also, note that:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, 和 the WHERE clause to restrict which rows you want in the result set.

And finally to answer your question regarding sub queries, from Rewriting Subqueries as Joins:

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server 和 many other modern database systems offer a wide range of outer join types.
mysql