The Wayback Machine - https://web.archive.org/web/20071011021922/http://sqlcourse2.com:80/setoper.html


Free Newsletters:
DatabaseJournal
DBANews
SQLCourse2
Advanced Online SQL Training		 Search Database Journal:
 
HOME News MS SQL Oracle DB2 Access MySQL PHP SQL Etc Scripts Links Forums DBA Talk
internet.com
SQL Courses
1 Start Here - Intro
2 SELECT Statement
3 Aggregate Functions
4 GROUP BY clause
5 HAVING clause
6 ORDER BY clause
7 Combining Conditions & Boolean Operators
8 IN and BETWEEN
9 Mathematical Functions
10 Table Joins, a must
11 SQL Interpreter
12 Advertise on SQLCourse.com
13 Other Tutorial Links
14 Technology Jobs


internet.commerce
Partner With Us
Server Racks
Promotional Products
Imprinted Promotions
Online Education
Online Booking Hotels
Corporate Gifts
Auto Insurance Quote
Laptop Computers
Memory
GPS Devices
Shop
Prepaid Phone Card
Web Design
Condos For Sale

internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers


eBook: Managing the Evolving Datacenter. Download this eBook to see how your datacenter can keep up. Learn more. (PDF)

IN and BETWEEN Conditional Operators


SELECT col1, SUM(col2)

FROM "list-of-tables"

WHERE col3 IN 
       (list-of-values);

SELECT col1, SUM(col2)

FROM "list-of-tables"

WHERE col3 BETWEEN value1 
AND value2;

The IN conditional operator is really a set membership test operator. That is, it is used to test whether or not a value (stated before the keyword IN) is "in" the list of values provided after the keyword IN.

For example:


SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

This statement will select the employeeid, lastname, salary from the employee_info table where the lastname is equal to either: Hernandez, Jones, Roberts, or Ruiz. It will return the rows if it is ANY of these values.

The IN conditional operator can be rewritten by using compound conditions using the equals operator and combining it with OR - with exact same output results:


SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts'
OR lastname = 'Ruiz';

As you can see, the IN operator is much shorter and easier to read when you are testing for more than two or three values.

You can also use NOT IN to exclude the rows in your list.

The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN.

For example:


SELECT employeeid, age, lastname, salary

FROM employee_info
 
WHERE age BETWEEN 30 AND 40;

This statement will select the employeeid, age, lastname, and salary from the employee_info table where the age is between 30 and 40 (including 30 and 40).

This statement can also be rewritten without the BETWEEN operator:


SELECT employeeid, age, lastname, salary

FROM employee_info

WHERE age >= 30 AND age <= 40;

You can also use NOT BETWEEN to exclude the values between your range.

Use these tables for the exercises
items_ordered
customers

Review Exercises

  1. Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.
  2. Select the firstname, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.

Click the exercise answers link below if you have any problems.

Answers to these Exercises

Enter SQL Statement here:

SQL Course 2 Curriculum
<<previous 1 2 3 4 5 6 7 8 9 10 11 12 13 14  next>>


Get FREE Intel Mobility Resources!
WHITEPAPER:
Increase Performance, Security with Intel Centrino Pro Processor Technology
 Get 2x performance on your notebooks, with innovative power-saving features designed to extend battery life.

WHITEPAPER:
Mobility Speeds Recovery from a Flood
 A disaster recovery strategy that included wireless-enabled notebook computers paid off.

WHITEPAPER:
Applying RFID Technology in High Volume Manufacturing
WHITEPAPER:
Using VoIP to Keep Corporate Travelers Connected to their Desktops
 Intel IT piloted a "soft phone" solution that lets employees access their phone extensions over the Internet.

Solutions


JupiterOnlineMedia

internet.comearthweb.comDevx.commediabistro.comGraphics.com

Search:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info


Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers