在SQL中，是否对连接进行分组，这有关系吗?

I do join 2 tables via SQL and add where clauses. The join is done by a condition in the where clause. I wonder if it makes a difference whether the where clauses expect the join clause are grouped by brackets.

我确实通过SQL连接两个表并添加where子句。连接是由where子句中的一个条件完成的。我想知道where子句期望的join子句是否按括号分组，这是否有区别。

To ask with examples: is example 1 equivalent to example 2 and example 3?

用例子提问:例1是否等价于例2和例3?

Example 1 (no grouping):

示例1(没有分组):

SELECT * FROM employees, vacation 
WHERE employees.first_name = 'Maria' and vacation_start > 2017 
    AND employees.employee_id = vacation.employee_id

Example 2 (everything except join clause is grouped):

例2(除join子句外的所有内容都被分组):

SELECT * FROM employees, vacation 
WHERE (employees.first_name = 'Maria' and vacation_start > 2017) 
    AND employees.employee_id = vacation.employee_id

Example 3 (join clause is first where argument):

示例3 (join子句是第一个where参数):

SELECT * FROM employees, vacation 
WHERE employees.employee_id = vacation.employee_id 
    AND (employees.first_name = 'Maria' and vacation_start > 2017)

I always assumed that databases will optimize this kind of queries. But do they? I'm primarily working with MariaDB and SQLite.

我总是假设数据库会优化这种查询。但这样做的结果呢?我主要和MariaDB和SQLite一起工作。

3 个解决方案

#1

Yes they are equivalent. But you should use explicit join instead of old WHERE syntax:

是的他们是等价的。但是你应该使用显式连接而不是旧的WHERE语法:

SELECT * 
FROM employees
JOIN vacation 
  ON employees.employee_id = vacation.employee_id
WHERE employees.first_name = 'Maria' and vacation_start > 2017;

Simple logic:

简单的逻辑:

-- AND has associative property
cond1 AND cond2 AND cond3
<=>
(cond1 AND cond2) AND cond3
<=>
cond1 AND (cond2 AND cond3)

#2

The order of where condition will not matter but the order of tables you are writing in joins does matter.
条件的顺序无关紧要，但是您在join中编写的表的顺序很重要。
If you keep the table with less records at left side of join will give better performance.
如果您将记录较少的表保存在join的左侧，将会获得更好的性能。
Regarding your WHERE condition, Optimizer will always push predicate down to make join operation faster. That means it will apply the conditions on tables first( employees.first_name = 'Maria' and vacation_start > 2017) and then perform join(employees.employee_id = vacation.employee_id) on filtered record set.

关于WHERE条件，优化器总是将谓词下推以使连接操作更快。这意味着它将首先应用于表上的条件(员工。first_name = 'Maria'和vacation_start > 2017)，然后执行join(员工)。在过滤记录集上的employee_id = vac.employee_id)。

If you check the explain plan for your query, you will understand it more.

如果您检查查询的explain计划，您将更了解它。

#3

Grouping in where clause firstly matters only for logical expressions in your case. For instance;

在where子句中分组首先只对逻辑表达式起作用。例如;

(A and B and C)

(A、B及C)

and

和

A and (B and C)

A和(B和C)

are equivalent.

是等价的。

But

但

(A or B and C)

(A或B及C)

and

和

(A or B) and C

(A或B)和C

are different.

是不同的。

In your example all queries are the same. A little performance issues may occur with your grouping choices if you have very big data. If not no problem.

在您的示例中，所有查询都是相同的。如果您有非常大的数据，那么您的分组选择可能会出现一些性能问题。如果不是没有问题。

#1