{"id":1221,"date":"2018-01-16T20:42:05","date_gmt":"2018-01-17T03:42:05","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1221"},"modified":"2025-01-19T02:13:21","modified_gmt":"2025-01-19T09:13:21","slug":"sql-logical-operators","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-logical-operators\/","title":{"rendered":"SQL Logical Operators"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL logical operators and how to use them to test for the truth of a condition.<\/p>\n\n\n\n

A logical operator allows you to test for the truth of a condition. Similar to a comparison operator<\/a>, a logical operator returns a value of true, false, or unknown.<\/p>\n\n\n\n

The following table illustrates the SQL logical operators:<\/p>\n\n\n\n

Operator<\/strong><\/th>Meaning<\/strong><\/th><\/tr><\/thead>
ALL<\/a><\/td>Return true if all comparisons are true<\/td><\/tr>
AND<\/a><\/td>Return true if both expressions are true<\/td><\/tr>
ANY<\/a><\/td>Return true if any one of the comparisons is true.<\/td><\/tr>
BETWEEN<\/a><\/td>Return true if the operand is within a range<\/td><\/tr>
EXISTS<\/a><\/td>Return true if a subquery contains any rows<\/td><\/tr>
IN<\/a><\/td>Return true if the operand is equal to one of the value in a list<\/td><\/tr>
LIKE<\/a><\/td>Return true if the operand matches a pattern<\/td><\/tr>
NOT<\/a><\/td>Reverse the result of any other Boolean operator.<\/td><\/tr>
OR<\/a><\/td>Return true if either expression is true<\/td><\/tr>
SOME<\/a><\/td>Return true if some of the expressions are true<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

AND #<\/a><\/h2>\n\n\n\n

The AND<\/a><\/code> operator allows you to construct multiple conditions in the WHERE<\/a><\/code> clause of an SQL statement such as SELECT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code>:<\/p>\n\n\n

expression1 AND expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AND<\/code> operator returns true if both expressions evaluate to true.<\/p>\n\n\n\n
Try it<\/p>\n\n\n\n
The following example finds all employees whose salaries are greater than 5,000 and less than 7,000:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary > 5000<\/span>\n  AND<\/span> salary < 7000<\/span>\nORDER<\/span> BY<\/span>\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
OR #<\/a><\/h2>\n\n\n\n
Similar to the AND<\/code> operator, the OR<\/a><\/code> operator combines multiple conditions in an SQL statement’s WHERE<\/a><\/code> clause:<\/p>\n\n\n
expression1 OR expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement finds employees whose salary is either 7,000 or 8,000:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary = 7000<\/span>\n  OR<\/span> salary = 8000<\/span>\nORDER<\/span> BY<\/span>\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
IS NULL #<\/a><\/h2>\n\n\n\n
The IS NULL<\/a><\/code> operator compares a value with a null value and returns true if the compared value is null; otherwise, it returns false.<\/p>\n\n\n\n
For example, the following statement finds all employees who do not have a phone number:<\/p>\n\n\n
SELECT<\/span> \n    first_name, last_name, phone_number\nFROM<\/span>\n    employees\nWHERE<\/span>\n    phone_number IS<\/span> NULL<\/span>\nORDER<\/span> BY<\/span> first_name , last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
BETWEEN #<\/a><\/h2>\n\n\n\n
The BETWEEN<\/a><\/code> 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.<\/p>\n\n\n\n
For example, the following statement finds all employees whose salaries are between 9,000 and 12,000.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary BETWEEN<\/span> 9000<\/span> AND<\/span> 12000<\/span>\nORDER<\/span> BY<\/span>\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Notice that the value 9,000 and 12,000 are included in the output.<\/p>\n\n\n\n
IN #<\/a><\/h2>\n\n\n\n
The IN<\/code> operator compares a value to a list of specified values. The IN<\/code> operator returns true if the compared value matches at least one value in the list; otherwise, it returns false.<\/p>\n\n\n\n
The following statement finds all employees who work in the department id 8 or 9.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id IN<\/span> (8<\/span>, 9<\/span>)\nORDER<\/span> BY<\/span>\n  department_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
LIKE #<\/a><\/h2>\n\n\n\n
The LIKE<\/a><\/code> operator compares a value to similar values using a wildcard operator. SQL provides two wildcards used in conjunction with the LIKE<\/code> operator:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
However, the OR<\/a><\/code> operator returns true if a least one expression evaluates to true.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n