Beginning SQL the SEQUEL: Working with Advanced SQL Statements - The IN-credible Statement (
Page 3 of 4 )
By now you may have noticed that there are multiple ways to return the same results in SQL. This is true for all languages really. With the IN statement, you can retrieve a specific set of data you are seeking.
SELECT * from EMPLOYEES
WHERE LastName IN ('Sampson','Lee');
With this line of coding, we are asking the database to return all rows that contain the last names of Sampson and Lee. I know what you are thinking; we could have done that with the OR operator or even the AND operator. Right you are. So why use IN? It's just a matter of preference.
BETWEEN AND
You have probably been between a rock and a hard place before, but that's not really what the BETWEEN AND statement is about. If you think about being asked to choose a number between 1 and 100, you're much closer to understanding this bit of SQL. The basic function of the BETWEEN AND statement is to allow you to choose a range of values.
Let's say you wanted to list all of the employees who made between $75,000 and $100,000. To do so, you would use the following code:
SELECT * from EMPLOYEES WHERE SALARY
BETWEEN $75,000 AND $100,000;
That should bring up the following table:
|
First Name |
Last Name |
Position |
Salary |
|
Homer |
Sampson |
CEO |
$75,000 |
|
Homer |
Sampson |
CFO |
$75,000 |
|
Liza |
Sampson |
CTO |
$85,000 |
|
Larry |
Smith |
President |
$100,000 |
Note that in some databases, the BETWEEN AND Statement is taken more literally and would not find $75,000 or $100,000, but instead any number between them. In that event you would change the parameter to between $74,999 and $100,001.
