What are Comparison and Logical Operators in SQL?

Comparison and logical operators are used in the WHERE clause of a SQL statement (the WHERE clause is discussed in the next section). These operators assist in evaluating some conditions to fetch desired dataset. Suppose that you have a table named Contact having three records as shown in the following figure. These records are used in the “Example” column below to provide an overview about the comparison and logical operators that will be used thoroughly in upcoming exercises.

    Comparison Operators :

Operator	Description	Example
=	Equal to	SELECT * FROM Contact where Name = ‘Riaz Ahmed’; Returns record # 1
<> or != or ^=	Not Equal to	SELECT * FROM Contact where Name <> ‘Riaz Ahmed’; Returns record # 2
>	Greater than	SELECT * FROM Contact where Age > 25; Returns record # 1
>=	Greater than or equal	SELECT * FROM Contact where Age >= 25; Returns record # 1 and 2
<	Less than	SELECT * FROM Contact where Id < 2; Returns record # 1
<=	Less than or equal	SELECT * FROM Contact where Id <= 2; Returns record # 1 and 2
BETWEEN … AND …	Data range between two values	SELECT * FROM Contact where Id BETWEEN 2 AND 100; Returns record # 2and 99
LIKE	Matches a pattern	SELECT * FROM Contact where message LIKE ‘%feed%’; Returns record # 1
IN	Search multiple values	SELECT * FROM Contact where name IN (‘Riaz Ahmed’, ’Daniel Clarke’); Returns record # 1 and 2
IS NULL	Fetches null values	SELECT * FROM Contact where name IS NULL Returns record # 3

    Logical Operators :

Operator	Description	Example
AND	Both conditions must evaluate to true	SELECT * FROM Contact where name=’Riaz Ahmed’ AND age=30; Returns record # 1
OR	Either condition returns true	SELECT * FROM Contact where name=’Riaz Ahmed’ OR age=25; Returns record # 1 and 2
NOT	Evaluates the opposite condition	SELECT * FROM Contact where name IS NOT NULL Returns record # 1 and 2
AND OR combined	Can be used in the same logical expression	SELECT * FROM Contact where name=’Riaz Ahmed’ AND (age=25 OR age=30 OR age=99); Returns record # 1

NOTE

When used in conjunction with the four SQL operators (and with the arithmetic operator), the NOT logical operator produces negating results. For instance, if you add the NOT operator to the above examples as follows, you will get opposing records.

     NOT Name = ‘Riaz Ahmed’

     NOT BETWEEN 2 AND 100

     NOT LIKE ‘%feed%’

     NOT IN (‘Riaz Ahmed’, ’Daniel Clarke’)

     name IS NOT NULL

In MariaDB, you can use the NOT operator to negate BETWEEN, IN, and EXISTS clauses as compared to other DBMS where it can be used to negate any conditions.

If the value being compared is a character string, (for example, Riaz Ahmed), or a date, then enclose it under single quotation makrs; numbers hould be entered without quotes.

Precedence Rules for Comparison and Logical Operators

Comparison and logical operators also follow some precedence rules as followed by the arithmetic operators. The following table lists the evaluation order for these operators.

Evaluation Order	Operators
1	All comparison Operators (=, <>, >, >=, <, <=, BETWEEN, LIKE, IN, IS NULL)
2	AND
3	OR

·         Comparison operators are evaluated first, even in negating expressions.

·         AND has a higher precedence over OR.

·         Equal precedence operators are evaluated from left to right.

Similar to the arithmetic operators, precedence for these operators can also be overridden by placing part of an expression in parentheses. Expression enclosed in parentheses are evaluated first, as demonstrated in the last example in the Logical Operators section above.