I'm not able to figure out the difference between these queries. I'm pretty sure that the first one is an equi-join. I'm not sure how the second one ISN'T the same as the first. The sub query in #2 selects all Ids from S, and then it returns all R's that also have those ID's, no?
我无法弄清楚这些查询之间的区别。我很确定第一个是equi-join。我不确定第二个与第一个不一样。 ＃2中的子查询从S中选择所有ID，然后返回所有具有这些ID的R，不是吗？
SELECT R.cname FROM R, S, WHERE R.Id = S.Id
SELECT R.cname FROM R，S，WHERE R.Id = S.Id
SELECT R.cname FROM R WHERE R.Id IN (SELECT S.Id FROM S)
SELECT R.cname FROM R WHERE R.Id IN（SELECT S.Id FROM S）
EDIT:
i grabbed this off a past exam....and I quote:
Although the queries seem to produce the same answers they don’t. How do they differ? You can give example instances of R and S that show that these two queries can produce different answers.
R (Id, TourId, cname, caddress, cost)
S (Id, SpecId)
编辑：我从过去的考试中抓住了这个....我引用：虽然查询似乎产生相同的答案，但他们没有。他们有什么不同？您可以给出R和S的示例实例，这些实例表明这两个查询可以产生不同的答案。 R（Id，TourId，cname，caddress，cost）S（Id，SpecId）
EDIT: EDIT:
I'm guessing it has to do with duplicates.
编辑：编辑：我猜它与重复有关。
3 个解决方案
#1
1
Assuming these data:
假设这些数据：
R
id cname
-- --
1 RA
1 RB
2 RC
S
id cname
-- --
1 SA
1 SB
2 SC
2 SD
, this query:
，这个查询：
SELECT R.cname
FROM R, S
WHERE R.Id = S.Id
returns
回报
RA
RA
RB
RB
RC
RC
, while this one:
，而这一个：
SELECT R.cname
FROM R
WHERE R.Id IN
(
SELECT S.Id
FROM S
)
returns
回报
RA
RB
RC
The first query is a JOIN of two tables: for each matching id it returns all possible combinations of the records from both tables
第一个查询是两个表的JOIN：对于每个匹配的id，它返回两个表中记录的所有可能组合
The seconds query is a one table with a predicate. It can return each record from R at most once.
秒查询是一个带谓词的表。它最多可以从R返回一次记录。
#2
2
IMO, both are poor choices because they are harder to read than:
国际海事组织，两者都是糟糕的选择，因为它们比以下更难阅读：
Select R.Naem
From R
Join S
On S.ID = R.ID
Beyond functionality, the paramount goal in writing code should be to make your intent clear for other readers. A developer has to read the comma in the first example and know that this implies a Cross Join. In the second, the reader has to determine why you wrote a subquery instead of simply writing a Join. Was there a performance reason? Was there reason with respect to the result?
除了功能之外，编写代码的首要目标应该是让其他读者明白你的意图。开发人员必须在第一个示例中读取逗号并知道这意味着交叉连接。在第二种情况下，读者必须确定为什么编写子查询而不是简单地编写Join。有性能原因吗？结果有理由吗？
Functionally, the two queries can produce different results depending on the data. Suppose we have the following:
从功能上讲，这两个查询可以根据数据产生不同的结果。假设我们有以下内容：
Create Table S ( Id int null )
Create Table R ( Id int null )
insert S Values(1)
insert S Values(1)
insert S Values(2)
insert R Values(1)
insert R Values(2)
insert R Values(3)
Select * From @R As R, @S As S Where R.Id = S.Id
This results in 1,1,2
这导致1,1,2
Select * From @R As R Where R.Id In( Select S.Id From @S As S )
This results in 1,2
这导致1,2
The second query will produce one row from R that has one or more values in S. The first query is akin to an Inner Join (even though it is using a Cross Join) which will return a row for each row in R and S that match. Since there are two rows in S that match a row in R, we get two rows for the value 1.
第二个查询将从R生成一行，在S中有一个或多个值。第一个查询类似于内部连接（即使它使用交叉连接），它将为R和S中的每一行返回一行比赛。由于S中有两行与R中的行匹配，因此我们得到值为1的两行。
#3
1
From application developer point of view both sub queries and join queries can work for you if you have well defined relation between tables (especially for join queries). But in general an equi join (or cross join which DB handles as equi join), can be faster as DB can optimize the join query. But in case of sub query, DB has to run both queries independently so you may see slightly slow performance. That being said, join can be slow if tales have too much data and sub queries may work in those case. general rule of thumb is to use join as and when possible. If you see some issue try sub query.
从应用程序开发人员的角度来看，如果表之间具有良好定义的关系（特别是对于连接查询），则子查询和连接查询都可以为您工作。但通常情况下，equi连接（或DB作为equi连接处理的交叉连接）可以更快，因为DB可以优化连接查询。但是在子查询的情况下，DB必须独立运行两个查询，因此您可能会看到性能稍慢。话虽如此，如果故事包含太多数据并且子查询可能在这种情况下起作用，则加入可能会很慢。一般的经验法则是尽可能使用join。如果你看到一些问题尝试子查询。
#1
1
Assuming these data:
假设这些数据：
R
id cname
-- --
1 RA
1 RB
2 RC
S
id cname
-- --
1 SA
1 SB
2 SC
2 SD
, this query:
，这个查询：
SELECT R.cname
FROM R, S
WHERE R.Id = S.Id
returns
回报
RA
RA
RB
RB
RC
RC
, while this one:
，而这一个：
SELECT R.cname
FROM R
WHERE R.Id IN
(
SELECT S.Id
FROM S
)
returns
回报
RA
RB
RC
The first query is a JOIN of two tables: for each matching id it returns all possible combinations of the records from both tables
第一个查询是两个表的JOIN：对于每个匹配的id，它返回两个表中记录的所有可能组合
The seconds query is a one table with a predicate. It can return each record from R at most once.
秒查询是一个带谓词的表。它最多可以从R返回一次记录。
#2
2
IMO, both are poor choices because they are harder to read than:
国际海事组织，两者都是糟糕的选择，因为它们比以下更难阅读：
Select R.Naem
From R
Join S
On S.ID = R.ID
Beyond functionality, the paramount goal in writing code should be to make your intent clear for other readers. A developer has to read the comma in the first example and know that this implies a Cross Join. In the second, the reader has to determine why you wrote a subquery instead of simply writing a Join. Was there a performance reason? Was there reason with respect to the result?
除了功能之外，编写代码的首要目标应该是让其他读者明白你的意图。开发人员必须在第一个示例中读取逗号并知道这意味着交叉连接。在第二种情况下，读者必须确定为什么编写子查询而不是简单地编写Join。有性能原因吗？结果有理由吗？
Functionally, the two queries can produce different results depending on the data. Suppose we have the following:
从功能上讲，这两个查询可以根据数据产生不同的结果。假设我们有以下内容：
Create Table S ( Id int null )
Create Table R ( Id int null )
insert S Values(1)
insert S Values(1)
insert S Values(2)
insert R Values(1)
insert R Values(2)
insert R Values(3)
Select * From @R As R, @S As S Where R.Id = S.Id
This results in 1,1,2
这导致1,1,2
Select * From @R As R Where R.Id In( Select S.Id From @S As S )
This results in 1,2
这导致1,2
The second query will produce one row from R that has one or more values in S. The first query is akin to an Inner Join (even though it is using a Cross Join) which will return a row for each row in R and S that match. Since there are two rows in S that match a row in R, we get two rows for the value 1.
第二个查询将从R生成一行，在S中有一个或多个值。第一个查询类似于内部连接（即使它使用交叉连接），它将为R和S中的每一行返回一行比赛。由于S中有两行与R中的行匹配，因此我们得到值为1的两行。
#3
1
From application developer point of view both sub queries and join queries can work for you if you have well defined relation between tables (especially for join queries). But in general an equi join (or cross join which DB handles as equi join), can be faster as DB can optimize the join query. But in case of sub query, DB has to run both queries independently so you may see slightly slow performance. That being said, join can be slow if tales have too much data and sub queries may work in those case. general rule of thumb is to use join as and when possible. If you see some issue try sub query.
从应用程序开发人员的角度来看，如果表之间具有良好定义的关系（特别是对于连接查询），则子查询和连接查询都可以为您工作。但通常情况下，equi连接（或DB作为equi连接处理的交叉连接）可以更快，因为DB可以优化连接查询。但是在子查询的情况下，DB必须独立运行两个查询，因此您可能会看到性能稍慢。话虽如此，如果故事包含太多数据并且子查询可能在这种情况下起作用，则加入可能会很慢。一般的经验法则是尽可能使用join。如果你看到一些问题尝试子查询。