sql - SQL减少联接中或其中的数据(SQL reduce data in join or where)

Question

I want to know what is faster, assuming I have the following queries and they retrieve the same data

(我想知道更快，假设我有以下查询，并且它们检索相同的数据)

select * from tableA a inner join tableB b on a.id = b.id where b.columnX = value

or

(要么)

select * from tableA inner join (select * from tableB where b.columnX = value) b on a.id = b.id

I think makes sense to reduce the dataset from tableB in advanced, but I dont find anything to backup my perception.

(我认为在高级情况下减少来自tableB的数据集是有意义的，但是我没有发现任何东西可以支持我的看法。)

  ask by André De La O Campos translate from so

Answer 1

In a database such as Teradata, the two should have exactly the same performance characteristics.

(在Teradata之类的数据库中，两者应具有完全相同的性能特征。)

SQL is not a procedural language.

(SQL不是过程语言。)

A SQL query describes the result set.

(SQL查询描述了结果集。)

It does not specify the sequence of actions.

(它没有指定操作顺序。)

SQL engines process a query in three steps:

(SQL引擎通过三个步骤处理查询：)

1. Parse the query.

   (解析查询。)

2. Optimize the parsed query.

   (优化解析的查询。)

3. Execute the optimized query.

   (执行优化的查询。)

The second step gives the engine a lot of flexibility.

(第二步为引擎提供了很大的灵活性。)

And most query engines will be quite intelligent about ignoring subqueries, using indexes and partitions based on where clauses, and so on.

(而且大多数查询引擎在忽略子查询，使用基于where子句的索引和分区等方面将非常聪明。)