如果我们使用<>内连接中的运算符。然后它是如何工作的。 [英] If we use <> operator in inner join. Then how it works.

问题描述

我们有2个表：TblState，TblCity。表TblState具有StateID，State列。表TblCity具有StateID，CityID，City列。现在我的问题是：



我想得到那些未在TblCity中使用连接的状态。



我尝试过：



从TblState中选择TblState.StateID，TblState.State，其中TblState.StateID NOT IN（从中选择StateID） TblCity）



这个工作正常。但我希望这个结果使用连接。我试过跟随，但没有得到正确的结果。



从TblState选择TblState.StateID，TblState.State内部加入TblCity ON TblState.StateID<> TblCity.StateID

we have 2 tables: TblState, TblCity. Table TblState have StateID, State column. Table TblCity have StateID, CityID, City column. Now my question is:

I want to get those state which are not used in TblCity using joins.

What I have tried:

Select TblState.StateID, TblState.State from TblState where TblState.StateID NOT IN (Select StateID from TblCity)

This working fine. but i want this result using joins. i tried following, but not getting proper result.

Select TblState.StateID, TblState.State from TblState Inner Join TblCity ON TblState.StateID <> TblCity.StateID

推荐答案

如果它工作正常，你为什么要改变它？

这是一个单独留下的情况之一更好，因为这个批次的LEFT JOIN版本比你当前使用的NOT IN慢约4％。



我设置了一些 #Temp 这个表测试这两个头2头，并查看了实际执行计划，发现比较是49/51，支持第一个语句

If it's working fine, why do you want to change it?
This is one of those cases where leaving alone is better, as the LEFT JOIN version of this batch is about 4% slower than the NOT IN you are currently using.

I set up some #Temp tables for this to test these two head-2-head, and reviewed the Actual Execution Plans to find that the comparison is 49/51 in favor of the first statement

SELECT StateID, [Name]
FROM   #tblState
WHERE  StateID NOT IN ( SELECT c.StateID FROM #tblCity c )

SELECT    s.StateID, s.[Name]
FROM      #tblState s
LEFT JOIN #tblCity  c ON s.StateID = c.StateID
WHERE     c.StateID IS NULL

我也测试了使用EXCEPT运算符，发现它比它上面的JOIN或IN变体慢了约50％（分裂是28/29/43）

I did also test utilizing the EXCEPT operator, and found this to be about 50% slower than either the JOIN or IN variants above it (split was 28/29/43)

SELECT s.StateID  FROM #tblState s
  EXCEPT
SELECT c.StateID  FROM #tblCity c

请注意，这只会返回StateID而不是实际的StateName。所以这需要在IN语句或JOINed中。要么进一步降低性能。添加到之前的3个查询中，执行计划拆分变为17-17-26-41

Please note that this will only return the StateID and not the actual StateName. So this would either need to be in an IN statement or JOINed. Either will further reduce performance. Added onto the previous 3 queries the Execution Plan splits become 17-17-26-41

SELECT StateID, [Name]
FROM   #tblState
WHERE  StateID IN (
         SELECT s.StateID  FROM #tblState s
           EXCEPT
         SELECT c.StateID  FROM #tblCity c
)

现在这些都在没有主键的表上。我确实添加了它们以及#City表中StateID列的索引。这加快了速度。 JOIN vs IN vs EXCEPT将实际执行计划更改为几乎相同：33/34/33。

在EXCEPT查询中添加IN，分割为22/22/22/34。

Now these are on tables without Primary Keys. I did add them in as well as an Index on the StateID column in the #City table. This sped things up considerable. JOIN vs IN vs EXCEPT changes the Actual Execution Plans to be virtually identical: 33/34/33.
With an IN added onto the EXCEPT query the split is 22/22/22/34.

使用LEFT OUTER JOIN或LEFT JOIN，并检查NULLS - 这些是你想要的行。

SQL LEFT JOIN vs LEFT OUTER JOIN |示例 [ ^ ]但是你'使用EXCEPT可能会更好： EXCEPT和INTERSECT（Transact-SQL） - SQL Server Microsoft Docs [ ^ ]

Use a LEFT OUTER JOIN or a LEFT JOIN, and check for NULLS - those are the rows you want.
SQL LEFT JOIN vs LEFT OUTER JOIN | Examples[^] but you'd probably be better off with an EXCEPT instead: EXCEPT and INTERSECT (Transact-SQL) - SQL Server | Microsoft Docs[^]

这篇关于如果我们使用&lt;&gt;内连接中的运算符。然后它是如何工作的。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！