SQL Logical Operators

Summary: in this tutorial, you will learn about the SQL logical operators and how to use them to test for the truth of a condition.

A logical operator allows you to test for the truth of a condition. Similar to a comparison operator, a logical operator returns a value of true, false, or unknown.

The following table illustrates the SQL logical operators:

AND

The AND operator allows you to construct multiple conditions in the WHERE clause of an SQL statement such as SELECT, UPDATE, and DELETE:

expression1 AND expression2Code language: SQL (Structured Query Language) (sql)

The AND operator returns true if both expressions evaluate to true.

The following example finds all employees whose salaries are greater than 5,000 and less than 7,000:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary > 5000 AND salary < 7000
ORDER BY salary;Code language: SQL (Structured Query Language) (sql)

OR

Similar to the AND operator, the OR operator combines multiple conditions in an SQL statement’s WHERE clause:

expression1 OR expression2Code language: SQL (Structured Query Language) (sql)

However, the OR operator returns true if a least one expression evaluates to true.

For example, the following statement finds employees whose salary is either 7,000 or 8,000:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary = 7000 OR salary = 8000
ORDER BY salary;Code language: SQL (Structured Query Language) (sql)

IS NULL

The IS NULL operator compares a value with a null value and returns true if the compared value is null; otherwise, it returns false.

For example, the following statement finds all employees who do not have a phone number:

SELECT 
    first_name, last_name, phone_number
FROM
    employees
WHERE
    phone_number IS NULL
ORDER BY first_name , last_name;
Code language: SQL (Structured Query Language) (sql)

BETWEEN

The BETWEEN operator searches for values that are within a set of values, given the minimum value and maximum value. Note that the minimum and maximum values are included as part of the conditional set.

For example, the following statement finds all employees whose salaries are between 9,000 and 12,000.

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 9000 AND 12000
ORDER BY salary;    
Code language: SQL (Structured Query Language) (sql)

Notice that the value 9,000 and 12,000 are included in the output.

IN

The IN operator compares a value to a list of specified values. The IN operator returns true if the compared value matches at least one value in the list; otherwise, it returns false.

The following statement finds all employees who work in the department id 8 or 9.

SELECT 
    first_name, last_name, department_id
FROM
    employees
WHERE
    department_id IN (8, 9)
ORDER BY department_id;
Code language: SQL (Structured Query Language) (sql)

LIKE

The LIKE operator compares a value to similar values using a wildcard operator. SQL provides two wildcards used in conjunction with the LIKE operator:

• The percent sign ( %) represents zero, one, or multiple characters.
• The underscore sign ( _) represents a single character.

The following statement finds all employees whose first name starts with the string jo:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    first_name LIKE 'jo%'
ORDER BY first_name;
Code language: SQL (Structured Query Language) (sql)

The following example finds all employees with the first names whose the second character is  h:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    first_name LIKE '_h%'
ORDER BY first_name;
Code language: SQL (Structured Query Language) (sql)

ALL

The ALL operator compares a value to all values in another value set. The ALL operator must be preceded by a comparison operator and followed by a subquery.

The following illustrates the syntax of the ALL operator:

comparison_operator ALL (subquery)
Code language: SQL (Structured Query Language) (sql)

Note that you will learn about the subquery in the subquery tutorial.

The following example finds all employees whose salaries are greater than all salaries of employees in the department 8:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary >= ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            department_id = 8)
ORDER BY salary DESC;Code language: SQL (Structured Query Language) (sql)

ANY

The ANY operator compares a value to any value in a set according to the condition as shown below:

comparison_operator ANY(subquery)Code language: SQL (Structured Query Language) (sql)

Similar to the ALL operator, the ANY operator must be preceded by a comparison operator and followed by a subquery.

For example, the following statement finds all employees whose salaries are greater than the average salary of every department:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary > ANY(SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY first_name , last_name; 
Code language: SQL (Structured Query Language) (sql)

Note that SOME is an alias for ANY, therefore, you can use them interchangeably.

EXISTS

The EXISTS operator tests if a subquery contains any rows:

EXISTS (subquery)Code language: SQL (Structured Query Language) (sql)

If the subquery returns one or more rows, the result of the EXISTS is true; otherwise, the result is false.

For example, the following statement finds all employees who have dependents:

SELECT 
    first_name, last_name
FROM
    employees e
WHERE
    EXISTS( SELECT 
            1
        FROM
            dependents d
        WHERE
            d.employee_id = e.employee_id);
Code language: SQL (Structured Query Language) (sql)

Now you should have a brief overview of all SQL logical operators and how to use them to test the truth of a condition. In the next tutorials, you will learn about each logical operator in detail.