Advanced comparison operators

In the last lesson we saw the basic comparison operators used with there WHERE clause. We have additional operators that can be used to make some of the complex queries easier. In this lesson we will look at using IN, NOT, BETWEEN, IS NULL and IS NOT NULL, and LIKE.

IN

The IN operators allows us to select records with multiple field values. We can do this with the OR operator, but that may become complicated when a large amount of values are needed. Let’s take for example querying the records from a student table where the ages of students are 18, 21, 25, 28, and 30. We could write a query as follows:

SELECT *
FROM student
WHERE age IN (18, 21, 25, 28, 30);

NOT

The NOT operator works similar to the IN operator but rather shows records that do not contain the selected values. So the following query will show all records of students that are not aged 18, 21, 25, 28, and 30.

SELECT *
FROM student
WHERE age NOT (18, 21, 25, 28, 30);

BETWEEN

Using the BETWEEN operator we can select a range of values for criteria. This range is inclusive which means that the values at the beginning and end of the range are included. The values in the range can be numbers, text, or dates. For example let’s say we want to show the students aged 30 through 35, we would write the query:

SELECT *
FROM student
WHERE age BETWEEN 30 and 35;

IS NULL and IS NOT NULL

The IS NULL operator is used to display records that have no value set for a field. The IS NOT NULL is used to display records that have a value set for a field. These operators can be used to find records that may need additional information added. To list the students who have not entered a middle name in the table we could write this query:

SELECT *
FROM student
WHERE middle_name IS NULL;

LIKE

The LIKE operator lets us use a wildcard with the WHERE clause. We can find records that match a pattern we define. Below are the wildcards we can use with LIKE.

Wildcard Description
% Is used to substitute for zero or more characters
_ Is used to substitute a single character
[charlist] Allows for a range of characters to match
[!charlist]  Allows for a range of character to not match

If we want to find all students that have a last name that starts with “P” we would write:

SELECT *
FROM student
WHERE last_name LIKE "P%";

If we want to find all students that have a last name that starts with “P” and ends with “N” we would write:

SELECT *
FROM student
WHERE last_name LIKE "P%N";

If we wanted to find students that have the character pattern “at” within the first name we would write:

SELECT *
FROM student
WHERE first_name LIKE "%at%";

This would return results like: Katelyn, Nathan, Kathy, Cathy, Matthew, Kathryn, etc. The wild cards allow for any amount of characters before the pattern “at” and any amount of characters after the pattern.

Lesson Activities

  1. Select the students who have a last name that begins with “S”. Show only the first_name and last_name fields.
    Click to see code
    SELECT first_name, last_name
    FROM student
    WHERE last_name LIKE "S%";
  2. Select the students who have a last name that begins with “S” or who are male. Show only the first_name and last_name fields.
    Click to see code
    SELECT first_name, last_name
    FROM student
    WHERE last_name LIKE "S%" OR sex = "M";
  3. Select the students who have no information set for the program_id field. Show the student_id, first_name, last_name, and program_id fields.
    Click to see code
    SELECT student_id, first_name, last_name, program_id
    FROM student
    WHERE program_id IS NULL;

Questions