不明白为什么在sql中过滤需要内部连接

Question

我有以下表格：

基本上，我使用连接表many2many在学生和课程之间建立了一个students_courses关系。

​

​

以下是表中填充的一些数据：

​

​

课程

​

​

students_courses：

​

​

所以基本上，我想为一个给定的学生选择full_name和c_id。例如，对于有id=3的学生，我会有Aurica 5和Aurica 6。

我的第一个方法是写：

select s.full_name,sc.c_id from students s, students_courses sc
where sc.s_id=3

但我得到的是：

Aurica 5
Aurica 6
Aurica 5
Aurica 6
Aurica 5
Aurica 6

因此，它由students_courses表的行数来复制。现在我不知道为什么会这样。

如果我是一个SQL解析器，我将像这样解析它：“从students_courses获取students_courses的full_name，从学生那里获取full_name，如果students_course行尊重where过滤器，则显示它们”。

它不能使用join，但我真的不明白为什么内部联接是必要的。

select s.full_name, sc.c_id from students s
inner join students_courses sc
on sc.s_id=s.id and s.id=3;

请解释一下sql解析器如何解释第一个SQL，以及为什么使用join工作。

谢谢,

Answer 1

当您从两个表中选择信息时，它所做的是所有记录的交叉乘积，然后查找满足where子句的所有记录。你在学生表里有3张记录

id | full_name
---+----------
3  | Aurica
4  | Aurica
5  | Aurica

以及student_courses表中的6条记录。

s_is | c_id
-----+-----
3    | 5
3    | 6
4    | 7
4    | 8
5    | 9
5    | 10

所以在where语句之前，它创建了18个不同的记录。所以很容易看到，我将包括所有的列。

s.id | s.full_name | sc.s_id | sc.c_id
-----+-------------+---------+--------
3    | Aurica      | 3       | 5
3    | Aurica      | 3       | 6
3    | Aurica      | 4       | 7
3    | Aurica      | 4       | 8
3    | Aurica      | 5       | 9
3    | Aurica      | 5       | 10
4    | Aurica      | 3       | 5
4    | Aurica      | 3       | 6
4    | Aurica      | 4       | 7
4    | Aurica      | 4       | 8
4    | Aurica      | 5       | 9
4    | Aurica      | 5       | 10
5    | Aurica      | 3       | 5
5    | Aurica      | 3       | 6
5    | Aurica      | 4       | 7
5    | Aurica      | 4       | 8
5    | Aurica      | 5       | 9
5    | Aurica      | 5       | 10

在那里，它只显示cs.id=3的

s.full_name | sc.c_id
------------+--------
Aurica      | 5
Aurica      | 6
Aurica      | 5
Aurica      | 6
Aurica      | 5
Aurica      | 6

第二个查询比较了sc.s_id=s.id的值，只显示了那些值相同的查询以及c_id=3。

Answer 2

SQL解析器不会尝试猜测两个表是如何关联的。数据库引擎似乎有足够的信息，可以通过以下约束来解决这个问题，但是SQL有意不使用FK关系来决定如何连接表；您可能希望在将来的日期(例如为了提高性能)删除约束，并且不希望删除约束来更改连接的生成方式。DBA需要自由地更改索引和约束，而不必担心是否更改了查询返回的结果。

因为它不能依赖于有完整的信息来继续，所以SQL引擎不从事推断/猜测关系的工作。这取决于编写SQL的人来指定他们要加入的内容。如果您没有给它任何指示，告诉它如何连接表(使用join ON子句或WHERE子句)，那么它将创建一个交叉连接，这将给出重复的结果。

Answer 3

首先，SQL是一种基于集合的语言，您操作的是数据集，而不是单个(行)数据。

如果我是一个SQL解析器，我将像这样解析它：“从students_courses获取students_courses的full_name，从学生那里获取full_name，如果students_course行尊重where过滤器，则显示它们”。

在这里，您忽略了集合students_courses和学生，只考虑每一行数据，比如如果这一行尊重过滤器，给我所有的信息。

JOIN不过滤数据(这是WHERE所做的)，而是将其组合在一起。

当您从表A中SELECT时，您将请求A中的行集，它们都是。

当您从表A SELECT WHERE --一些条件--请求A中尊重该条件的行集(因此，SQL从A中丢弃不属于您在查询中描述的集合的行)。

当您使用JOIN table_a和table_b时，您要求将a中的行集与b中的行集合连接起来，获得一个新的集合，它的行是A中一行的“级联”(让我使用这个术语)，而列是B中的行；这没有给出关于如何连接行的任何其他信息，只会导致table_a的每一行与table_b的每一行连接。

这就是为什么你没有得到你所期望的。

最后，从概念的角度来看，我想指出的是，SQL引擎并不接受您从表或表中请求的列，而是在(1)加入了您请求的任何表中的行之后，(2)过滤掉了与where条件不匹配的任何行之后，它只返回从结果集的行(1)和(2)中请求的列。在现实生活中，RDBMS可以重新排序这些操作，并根据现有的索引和其他查询和表信息应用任何可能的优化。

这应该能让你对发生的事情有一个大致的了解。但是，正如@GordonLinoff所建议的那样，我认为在进一步研究之前，您应该对SQL和关系数据库有一个更好的基础，否则就会变得更加困难。

顺便提一句，您在FROM子句中拥有的是一种隐式连接，这是一种以前的联接语法，在这种语法中，FROM子句指定了所涉及的表，而WHERE子句则是连接谓词(其值应该匹配以连接数据的列)。