IN

• IN determines whether a specified value matches any value in a subquery or a list.
  • Any null values returned by subquery or expression that is compared to test_expression using IN or NOT IN return UNKNOWN.
  • Using null values in together with IN or NOT IN can produce unexpected results.
• Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632.
  • To work around this problem, store the items in the IN list in a temp table or table variable.

SELECT p.FirstName, p.LastName, e.JobTitle FROM Person.Person p
JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

SELECT p.FirstName, p.LastName FROM Person.Person AS p 
JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID IN 
(
	SELECT BusinessEntityID 
	FROM Sales.SalesPerson 
	WHERE SalesQuota > 250000
);

• Do not use batched parameterized queries for selecting rows by ID.
  • They should be replaced with a temporary table lookup.
• It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery.
  • However, the query optimizer now treats EXISTS and IN the same way, whenever it can, so you’re unlikely to see any significant performance differences.
  • Nevertheless, you need to be cautious when using the NOT IN operator if the subquery’s source data contains NULL values.
  • If so, you should consider using a NOT EXISTS operator instead of NOT IN, or recast the statement as a left outer join.

BETWEEN

• BETWEEN specifies a range to test.
• To specify an exclusive range, use the greater than (>) and less than operators (<).
• If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep ON e.BusinessEntityID = ep.BusinessEntityID
WHERE ep.Rate BETWEEN 27 AND 30
ORDER BY ep.Rate;

SELECT BusinessEntityID, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE RateChangeDate BETWEEN '20011212' AND '20020105';

LIKE

• LIKE determines whether a specific character string matches a specified pattern.
• If any of the arguments are not of character string data type, the Database Engine converts it to character string data type, if it is possible.

Wildcard characters

• %: Any string of zero or more characters.
  • WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title.
• _ (underscore): Any single character.
  • WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).
• [ ]: Any single character within the specified range ([a-f]) or set ([abcdef]).
  • WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with "arsen" and starting with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on.
  • In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
• [^]: Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
  • WHERE au_lname LIKE ‘de[^l]%’ all author last names starting with de and where the following letter is not l.
• escape_character is a character that is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
  • escape_character is a character expression that has no default and must evaluate to only one character.