SQL Transact - Where IN clause

SQL Transact – Where IN clause

Just a tiny word, but what a difference IN makes to your transact statement. On this page we combine WHERE and IN to make a more sophisticated SQL filter.

Lesson 1 – Filtering companies in specific countries.

The situation is: You need to contact your customers buy only if they are in 4 Central European countries.

— SQL statement to find customers in Austria, Belgium, Germany, and Italy

USE Northwind
SELECT companyname, country
FROM customers
WHERE country IN (‘Austria’, ‘Belgium’, ‘Germany’, ‘Italy’)
GO

Learning Points

Note 1: What I like is the efficiency of using SQL’s IN rather than multiple select statements.

Note 2: The choice of round brackets to (enclose all the countries).

Note 3: Each country has its own set of single quotes and is separated from its neibour by a comma.

Note 4: Incidentally, we filtered the columns to just companyname and country.

Another SQL Transact condition: Where BETWEEN

On a completely different tack, you could try:

Where BETWEEN

Example:

— SQL Transact statement to find prices between 15 and 25

USE Northwind
SELECT productname, unitprice
FROM products
WHERE unitprice BETWEEN 15 AND 25
GO

Guy’s Out Takes

If you like a challenge, troubleshoot this transact statement and see if you can correct the error.

SQL Transact Out Take 1

Why does this transact statement not execute?

— SQL Statement to find customers in Austria, Belgium, Germany, and Italy

USE Northwind
SELECT companyname, county
FROM customers
WHERE country IN (‘Austria’, ‘Belgium’, ‘Germany’, ‘Italy’)
GO

Answer: What wee need is countRy not county.

SQL Transact Out Take 2

Why won’t this statement run?

— SQL Statement to find prices between 15 and 25

USE Northwind
SELECT productname, unitprice
WHERE unitprice BETWEEN 15 AND 25
FROM products
GO

Answer: The FROM clause should be on the line before the WHERE clause: here is the correct SQL Transact sequence.

FROM Products

WHERE unitprice BETWEEN 15 AND 25.

SQL 2000 Transact – Where IN clause