SQL Logical Operators

Summary: in this tutorial, you will learn about 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:

Operator	Meaning
ALL	Return true if all comparisons are true
AND	Return true if both expressions are true
ANY	Return true if any one of the comparisons is true.
BETWEEN	Return true if the operand is within a range
EXISTS	Return true if a subquery contains any rows
IN	Return true if the operand is equal to one of the values in a list
LIKE	Return true if the operand matches a pattern
NOT	Reverse the result of any other Boolean operator.
OR	Return true if either expression is true
SOME	Return true if some of the expressions are true

AND

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

expression1 AND expression2

The AND the operator returns true if both expressions evaluate to be 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;

OR

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

expression1 OR expression2

However, the OR the 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;

IS NULL

The IS NULL the 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;

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;    

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

IN

The IN the 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;

LIKE

The LIKE the 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;

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

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    first_name LIKE '_h%'
ORDER BY first_name;

ALL

The ALL the 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)

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;

ANY

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

comparison_operator ANY(subquery)

Similar to the ALL operator, the ANY the 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; 

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)

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);

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.