Using IN clause in sql server

问题:

My query is like below.I want to select values if Type = 1 and subtype = 1,3 or 2.

select sum(case when Type = 1 and  SubType in (1, 3 or 2) then 1 else 0 end) as 'WorkStations'

Is this right way?

回答1:

Since you're only trying to get a count of the workstations that meet the criteria as far as I can see:

SELECT COUNT(*) AS Workstations FROM MyWorkStationTable WHERE Type = 1 AND SubType IN (1, 2, 3)

Also, an IN clause is by nature already an OR. It is neither valid syntax nor necessary to state it.

回答2:

If you're simply counting records, your best bet is to use the COUNT function provided by SQL Server. Consider using the following:

SELECT COUNT(*) FROM [Table] WHERE TYPE = 1
                              AND (SUBTYPE = 1
                               OR  SUBTYPE = 2
                               OR  SUBTYPE = 3)

It is best to avoid using 'IN' as it can lead to unnecessary calls to the SQL engine.

回答3:

SELECT COUNT(*) [Workstations] FROM [YourTable] t WHERE t.Type = 1 AND t.SubType IN (1, 2, 3) 

回答4:

Try avoiding IN Predicates and instead use Joins because it Iterate unnecessarily despite of the fact that there is just one/two match. I will explain it with an example.

Suppose I have two list objects.

List 1      List 2
  1           12
  2            7
  3            8
  4           98
  5            9
  6           10
  7            6

Using IN, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!