SQL Logical Operators
- Read
- Discuss
An operator in SQL is a reserved word or a character that is used to query our database in a SQL expression. SQL Logical Operators are necessary for learning SQL as they act as a connector between two or more conditions in an SQL query.
Logic operators in SQL are used to check whether the condition is true. These operators return a Boolean value of TRUE, FALSE, or UNKNOWN.
Most important logical operators that are used in advanced SQL are:

Important Logical Operators in SQL
1. AND Operator
The AND operator in SQL is used for data filtering and obtaining precise results based on conditions.
AND operator is used to combine two or more conditions and displays only those records in the result set where both the conditions condition1 and condition2 evaluates to True.
Syntax for AND Operator
SELECT column_name
FROM table_name
WHERE condition1 AND condition2;
Example for AND Operator
If we want to find records of all those owners who own a pet and are living in Southfield, we can write following query:
SELECT o.OwnerID,o.Name as OwnerName,p.PetID
FROM Owners as o, Pets as p
WHERE o.OwnerID=p.OwnerID AND o.City='Southfield';
Output:
2. OR Operator
The OR operator in SQL is used for data filtering and obtaining precise results based on conditions.
OR operator is used to combine two or more conditions and displays only those records in the result set where either one of the two conditions, condition1 and condition2, evaluates to True.
Syntax for OR Operator
SELECT column_name
FROM table_name
WHERE condition1 OR condition2;
Example for OR Operator
If we want to find records of all those owners who own a pet OR they are living in Southfield, we can write following query:
SELECT o.OwnerID,o.Name as OwnerName,p.PetID
FROM Owners as o, Pets as p
WHERE o.OwnerID=p.OwnerID OR o.City='Southfield';
Output:
3. BETWEEN Operator
The SQL BETWEEN operator allows you to select data within a range of values (inclusive).
This operator evaluates to TRUE when the column value is less than or equal to a high value and greater than or equal to a low value. The starting and ending value is included in the result set.
Syntax for BETWEEN Operator
SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example for BETWEEN Operator
In the example below, records of all pets having age between 7 and 13 (including 7 and 13) will be in the result set
If we want to find records of all the procedures that were carried out between 1/17/2016 and 1/22/2016, we can write following query:
SELECT * FROM ProceduresHistory a
WHERE Date BETWEEN '1/17/2016' AND '1/22/2016';
Output:
4. IN Operator
IN operator in SQL allows you to test if the value/expression lies in the list of values. It is used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE or DELETE. You can also use NOT IN to exclude the rows in your list.
Syntax for IN Operator
SELECT column_name
FROM table_name
WHERE column_name IN (list-of-values)
Example for IN Operator
If we want to find records of all those pets whose age in either 3,5,9 or 11, we can write following query:
SELECT * FROM Pets
WHERE Age IN (3,5,9,11);
Output:
5. Wildcard Operator
SQL wildcards are used to search for data within a table. Wildcard operators are used with the SQL LIKE operator.
Most important wildcards are:
% | A substitute for zero or more characters |
_ | A substitute for a single character |
LIKE Operator | Description |
WHERE CustomerName LIKE ‘a%’ | Finds any values that starts with “a” |
WHERE CustomerName LIKE ‘%a’ | Finds any values that ends with “a” |
WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position |
WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position |
WHERE CustomerName LIKE ‘a__%’ | Finds any values that starts with “a” and are at least 3 characters in length |
WHERE ContactName LIKE ‘a%o’ | Finds any values that starts with “a” and ends with “o” |
Syntax for Wildcard Operator
SELECT column_name
FROM table_name
WHERE column_name LIKE ‘abc%’;
Example for Wildcard Operator
If we want to find records of all those owners whose zip code starts with “49”, we can write following query:
SELECT column_name
FROM table_name
WHERE column_name LIKE ‘abc%’;
Output:
Leave a Reply
You must be logged in to post a comment.