SQL语句如何工作 [英] How Does SQL Statement Work

问题描述

我对执行此查询有疑问:

I have a question regarding the execution of this query:

SELECT
    student.id,
    student.name,
    student.class,
    teachers.name
FROM student 
INNER JOIN admin
    ON admin.id = student.id
WHERE
    student.id <> 0

我认为它首先执行以下语句:

I think it first executes the below statement:

SELECT
    student.id,
    student.name,
    student.class,
    teachers.name
FROM student
WHERE
    student.id <> 0

我认为我们将获得一个表，然后将从上述查询中获得的表与内部连接语句进行比较:

I think we will get a table, and then the table which we got from above query will compare with the inner join statement:

INNER JOIN admin
    ON admin.id = student.id

我的上述想法正确吗?

Am I correct in my above thinking?

推荐答案

不，您不正确.

有不同的算法，具体取决于数据库服务器，索引和数据顺序(集群PK)，计算值是否联接等.

因此，建议在此处 [此 [

No, you are not correct.

There are different algorithms, depending on the DB server, indexes and data order (clustered PK), whether calculated values are joined or not etc.

As, suggested here[^], have a look at a query plan, which most SQL systems can create for a query, it should give you an idea what it does.


For more read this[^].

这是sql server的执行顺序选择命令:
1. FROM子句
2. WHERE子句
3. GROUP BY子句
4. HAVING子句
5. SELECT子句
6. ORDER BY子句

如果再次查看该顺序，则可以得出结论，FROM是合乎逻辑的，因为首先在sql server上执行它可以显着缩小可能的记录集大小.然后它在哪里等等...

此外，SQL Server中提供了几个逻辑连接语句，但是sql使用三个"不同的物理运算符实现了这些逻辑运算符:
1.嵌套循环联接
2.合并加入
3.哈希联接
对我来说，使用哪一个也是一个秘密"；)，因为sql在何时以及使用哪种联接类型时具有内部逻辑.我们可以写有关这是一个非常广泛的主题"的文章，因此，如果您想了解有关此google的更多信息或找到一些有关此google的好的电子书， ;)
我的选择是"SQL Server MVP深入探讨"，它是许多SQL Server MVP的贡献，您已经解释了所有内容.

我希望至少能有所帮助. ;)

This is the execution order of sql server select command:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

If you look at the order again you can conclude that FROM is logical to be on first place, because executing it first sql server can dramatically narrow down possible record set sizes. Then it goes WHERE and so on...

Further more there is a several logical joins statements available in sql server, but sql implements these logical operators with "three" different physical operators:
1. Nested Loops Join
2. Merge Join
3. Hash Join
Which one of them is used is a "mistery" to me also ;), because sql have internal logic when and what join type to use. We could write about This is very wide topic, so if you want more information about this google or find some good e-book about it. ;)
My choice is "SQL Server MVP Deep Dives", it is a contributions from many SQL Server MVPs, and you have explained everything.

I hope that I helped at least a little. ;)

这篇关于SQL语句如何工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！