这两个查询在获得两个不同的结果集时有什么区别? [英] What is the difference in these two queries as getting two different result set?

问题描述

这两个查询得到不同的结果集，第二个结果集似乎是正确的.这些查询有何不同.

I am getting different result set for these two queries and second result set seems to be correct. What is the difference in these queries.

什么类型的内连接查询秒是?

What type of inner join query second is?

1)

FROM TABLE1 t1
 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
WHERE
 t1.StatusId = 12

2)

FROM TABLE1 t1
 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id
 AND t1.StatusId = 12

推荐答案

在 INNER JOIN 中执行此操作确实没有区别.

It really makes no difference when you do this in the INNER JOIN.

但是，当您使用 LEFT 或 RIGHT JOIN 时，确实是将附加过滤器放入 JOIN 还是放入 WHERE 子句中.

However, when you use LEFT or RIGHT JOIN, it does make a difference whether you put the additional filter into the JOIN or into the WHERE clause.

当您将过滤器放入 WHERE 子句时，SQL Server 首先进行连接，然后完全过滤掉过滤器不适合的行.
--> 这将减少返回的行数

When you put the filter into the WHERE clause, SQL Server does the join first, and then completely filters out the rows where the filter does not fit.
--> this will reduce the number of rows which are returned

当您将过滤器放入 JOIN 时，SQL Server 会在连接期间进行过滤，但仅在您放置过滤器的表上进行.
您仍然可以从其他表中获取所有行，但只有那些具有过滤器适合的过滤表中的数据.
--> 这不会减少行数，但过滤器表中的数据列在更多行中将是空的

When you put the filter into the JOIN, SQL Server does the filtering during the join, but only on the table where you put the filter.
You still get all the rows from the other tables, but only those have the data from the filtered table where the filter fits.
--> this will not reduce the number of rows, but the columns with data from the filteres table will be empty in more rows

很难解释……为了更清楚，这里有一个例子:

It's difficult to explain...to make it more clear, here's an example:

从 RedFilter 的回答:

CREATE TABLE [dbo].[t1](
    [ID] [int] NULL,
    [StatusID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[t2](
    [ID] [int] NULL
) ON [PRIMARY]
INSERT INTO t1 (ID, StatusID) VALUES (1, 10)
INSERT INTO t1 (ID, StatusID) VALUES (2, 11)
INSERT INTO t1 (ID, StatusID) VALUES (3, 12)
INSERT INTO t1 (ID, StatusID) VALUES (4, 12)
INSERT INTO t2 (ID) VALUES (1)
INSERT INTO t2 (ID) VALUES (3)
INSERT INTO t2 (ID) VALUES (5)

...并对其运行以下查询:

...and run the following queries on it:

/* this returns four rows, but only two will have data 
from the second table in the second column */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 

/* this returns only one row: the one where t2.ID = 1 */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 
WHERE t2.ID = 1 

/* this returns four rows as in the first query, but only one 
row will have data in the second column: the one where t2.ID = 1 */
SELECT t1.ID, t2.ID
FROM t1 
LEFT JOIN t2 ON t1.Id = t2.Id 
AND t2.ID = 1 

注意评论中指出的不同结果.

Note the different results as indicated in the comments.

这篇关于这两个查询在获得两个不同的结果集时有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！