MySql JOINS的优点/缺点 [英] Pros / Cons of MySql JOINS

问题描述

当我从多个表中选择数据时，我经常使用JOINS，最近我开始使用另一种方法，但是我不确定从长远来看会带来什么影响.

When I'm selecting data from multiple tables I used to use JOINS a lot and recently I started to use another way but I'm unsure of the impact in the long run.

示例:

SELECT * FROM table_1 LEFT JOIN table_2 ON (table_1.column = table_2.column)

这是跨表的基本LEFT JOIN，但请看下面的查询.

So this is your basic LEFT JOIN across tables but take a look at the query below.

SELECT * FROM table_1,table_2 WHERE table_1.column = table_2.column

就我个人而言，我可以说7个数据表，而不是JOINS.

Personally if I was joining across lets say 7 tables of data I would prefer to do this over JOINS.

但是这两种方法有什么优缺点?

推荐答案

第二种方法是INNER JOIN的快捷方式.

Second method is a shortcut for INNER JOIN.

 SELECT * FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column

仅选择在两个表中都符合条件的记录(LEFT JOIN将从左侧的表中选择所有记录，而从右侧的表中选择匹配的记录)

Will only select records that match the condition in both tables (LEFT JOIN will select all records from table on the left, and matching records from table on the right)

从 http://dev.mysql.com/doc引用/refman/5.0/en/join.html

[...]我们认为table_reference项列表中的每个逗号等同于内部联接

[...] we consider each comma in a list of table_reference items as equivalent to an inner join

还有

INNER JOIN和，(逗号)在没有连接条件的情况下在语义上等效:都在指定的表之间产生笛卡尔积(即，第一个表中的每一行都与该表中的每一行连接在一起)第二张表).

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

但是，逗号运算符的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等.如果在存在联接条件时将逗号联接与其他联接类型混合使用，则可能会出现"on子句"中形式为未知列" col_name"的错误.本节稍后将提供有关解决此问题的信息.

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

通常，这里提到了很多事情，应该使您考虑不使用逗号.

In general there are quite a few things mentioned there, that should make you consider not using commas.

这篇关于MySql JOINS的优点/缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！