SQL BETWEEN and IN Operator

We’ve seen that Between defines a range of values to check against for inclusion or exclusion from the result table. This is not always enough. What if we needed to check for certain values only? Values that do not always fit into a neat range. To accommodate this, SQL allows the use of the IN operator. In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the Where clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns.

The syntax for using the IN keyword is as follows:

SELECT column_name
FROM derived_table
WHERE column_name [NOT] IN (‘value1’, ‘value2′, … ,’value_n’)

This SQL statement will return the records where column1 is value1, value2…, or value_n. The number of values in the parenthesis can be one or more, and they can be numerical or characters.

Example #1

Select *
From EmployeeAddressTable
Where FirstName IN (‘Mary’, ‘Sam’)

This would return all rows where the FirstName is either Mary or Sam. Because the * is used in the select, all fields from the EmployeeAddressTable table would appear in the result set.

Example #2

SELECT *
FROM EmployeeAddressTable
WHERE FirstName = ‘Mary’
OR FirstName = ‘Sam’

It is equivalent to the previous statement. As you can see, using the IN function makes the statement easier to read and more efficient.

Example #3

Select *
From EmployeeAddressTable
Where FirstName NOT IN (‘Mary’, ‘Sam’)

This query uses the NOT operator to retrieve all records where the employee’s lastname is neither Mary nor Sam. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example #4

Select *
From EmployeeStatisticsTable
Where Benefits in (15000, 12000)

You can also use the IN function with numeric values. This SQL statement would return all Employees for which Benefits is either 15000 or 12000.

 

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

 


BETWEEN Operator Example

The “Persons” table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to select the persons with a last name alphabetically between “Hansen” and “Pettersen” from the table above.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName
BETWEEN ‘Hansen’ AND ‘Pettersen’

The result-set will look like this:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes

Note: The BETWEEN operator is treated differently in different databases!

In some databases, persons with the LastName of “Hansen” or “Pettersen” will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values.

In other databases, persons with the LastName of “Hansen” or “Pettersen” will be listed, because the BETWEEN operator selects fields that are between and including the test values.

And in other databases, persons with the LastName of “Hansen” will be listed, but “Pettersen” will not be listed (like the example above), because the BETWEEN operator selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator.


Example 2

To display the persons outside the range in the previous example, use NOT BETWEEN:

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN ‘Hansen’ AND ‘Pettersen’

The result-set will look like this:

P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s