使用关键字vs ON子句-MYSQL [英] USING Keyword vs ON clause - MYSQL

问题描述

可能重复:
MySQL启用还是使用?

Possible Duplicate:
MySQL ON vs USING?

查询1:

SELECT *
FROM users
JOIN orders ON (orders.user_id = users.user_id)
WHERE users.user_id = 1;

查询2:

SELECT *
FROM users
JOIN orders USING (user_id)
WHERE user_id = 1;

我想加入订单和用户表以获取某些数据.它工作正常.我的问题是，由于两个查询都输出相同的结果集，是否一样?使用哪一种效率更高?哪一个对性能有好处?最佳做法是哪一种?

I want to join orders and users tables to get some certain data. It works fine. My issue is since both queries output the same results set, is it the same? Which one is more efficient to be used? Which one is good for performance ? Which one is the best practise ?

推荐答案

从多个表中检索数据时，在JOIN条件下我们不需要提及USING子句.当我们使用USING子句时，两个表中都应存在该特定的列名，并且SELECT查询将使用USING子句中的给定列名自动联接这些表.

The USING clause is something we don't need to mention in the JOIN condition when we are retrieving data from multiple tables. When we use USING clause, that particular column name should be present in both tables, and the SELECT query will automatically join those tables using the given column name in USING clause.

例如如果表中有两个公共列名，则在USING子句

for e.g. if there are two common column names in the table, then Mention the desired common column name in the USING clause

USING.

例如

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
  USING dept_id; 

• USING子句:这使您可以按名称指定连接键.

  • The USING clause: This allows you to specify the join key by name.

    ON子句:此语法允许您为两个表中的连接键指定列名.

    The ON clause: This syntax allows you to specify the column names for join keys in both tables.

    USING子句

    如果多个列共享相同的名称，但您不想使用所有这些公共列进行联接，则使用USING子句.语句中USING子句中列出的列不能有任何限定符，包括WHERE子句.

    The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause.

    ON子句

    ON子句用于连接两个表中列名都不匹配的表.连接条件已从WHERE子句中的过滤条件中删除.

    The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause.

    这篇关于使用关键字vs ON子句-MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！