Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

The SQL IN and BETWEEN operators

Say for example that, you have a list of 30 employee numbers and would like to get information on them. Instead of writing a complex and long query validating each number with operators such as Like, you could simply use the IN operator. The syntax for using the IN operator is as follows:

Let us say we would like to get information on employees with employee numbers 123,4665, 4577 and 788, the simplest way to do it is to issue the following SQL query.

SELECT * FROM employee WHERE employee_num IN (123,4665, 4577,788);

This is with the assumption that employee number is an integer (that is why the numbers are not enclosed in single quotes). If employee numbers where characters, such values would have to be enclosed by single quotes.

The above query will return all columns of data from the employee table where employee numbers match any of the specified numbers.

If we did not want to use the IN operator, the other alternative would have been using a series of operators in a select statements like this:

SELECT *

      FROM employee
      WHERE employee_num = 123 OR
            employee_num = 4665 OR
	    employee_num = 4577 OR 
            employee_num = 788;

as you can see this is a much longer query than the last one.

Using NOT with IN operator

What if the list of numbers we had above was the list of employees that we did not want included in the results? We could simply use a combination of NOT and IN operators to exclude those results, for example:

SELECT * FROM employee WHERE employee_num NOT IN (123,4665, 4577,788);

returns the list of all employees in the database table except those with the specified employee numbers.

Using BETWEEN operator

This operator is used to return records within a specific range. Say for example that we wanted to return a list of employees whose dates of birth falls within a specific range of dates (15 July 1982 and 20 June 1983). We could issue the following query:

 SELECT * FROM employee 
WHERE dateofbirth BETWEEN ‘1982-07-15’ AND ‘1983-06-20’;

Note here that the operator BETWEEN is always used together with the Operator AND.

Alternatively, we could have issued the following query to produce the same outcome:

 SELECT * FROM employee WHERE dateofbirth >= ‘1982-07-15’ AND dateofbirth <= ‘1983-06-20’;

The results are the same, but some developers would prefer one approach over the other.

Just like the previous examples, to exclude the specified dates from the results set, you use the NOT operator with BETWEEN as follows:

 SELECT * FROM employee WHERE dateofbirth NOT BETWEEN ‘1982-07-15’ AND ‘1983-06-20’;

This returns all columns of employee table whose date of bith does not fall between the two dates.

SQL where Clause
As discussed in the posting Retrieving data from a database using SQL , the WHERE clause is used to restrict the result set to only the records that meet the specified criteria. Like we have already seen the use of where clause requires the keyword WHERE followed by the fields that yo

SQL SELECT Queries
So, we have created your database, added data into the tables and now what? In real world nobody collects data for the sake of collecting something. It has to be something that they can use at a later time. This section is dedicated to retrieving information stored in a da

Evaluating Algebraic Expressions - BODMAS
When evaluating algebraic expressions, you will need to follow the right order of operations in order to get the right answer. In this posting we will cover the approach we call BODMAS - which denotes the order inwhich operators are applied: BODMAS stands for (Brackets -> Or

Substitution and Evaluating

operators

PL/SQL expressions and Boolean operators
Expressions are part of PL/SQL that evaluates to something, but is not valid on its own. For example, an expression can appear on the right hand side of an assignment operator, but that expression can not be used on its own, it has to e part of another statement. For example, 2

Udjuni.com

The SQL IN and BETWEEN operators

Using NOT with IN operator

Using BETWEEN operator

RELATED ARTICLES: