0

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?

Suraj
  • 184
  • 1
  • 14

2 Answers2

0

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

Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

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.
audin
  • 84
  • 7