SQL - Between
Between is a special operator appearing in a where statement. It allows for the selection of a range of values between one value and another.
SQL Code:
SELECT * FROM table_name WHERE column_one
BETWEEN value1 AND value2;
Be aware that the values you specify will also return results. For example if we were looking for values between 5 and 10, all rows would be retrieved where our column value is 5,6,7,8,9, or 10.
SQL - In Between (Subqueries)
This next example requires that you are familiar with the previous lesson, SQL In. You may recall that the In operator allows the where clause to return more than one single value for a column. Below are two tables. The employee table only stores personal information about employees while the logbook records when and who accessed our database.
employees
| user_id | last | first |
| 0045 | Davis | Julie |
| 0048 | Thomas | David |
| 0067 | Smith | John |
| 0098 | Hodgensen | Bruce |
logbook
| user_id | timestamp |
| 0045 | 000000002345 |
| 0045 | 000000045666 |
| 0048 | 000000055767 |
| 0098 | 000000076444 |
Say we were thinking ahead when issuing user_ids and coordinated each user_id number with a department. For example our data security department employees will be numbered 004X, where X represents any number 0-9 (So data security employees are numbered 0040-0049). Since we konw this information it will prove useful if we want to track down database users by department.
SQL Code:
SELECT * FROM employees WHERE user_id IN
(SELECT user_id FROM logbook WHERE user_id BETWEEN 0040 AND 0049);
SQL will now retrieve exactly what we need. A listing of all the data security employees (user_id 004X) that have accessed the database thus far.
| 
