Using the LIKE Operator in SQL

In many situations you search for records in your database whose exact values are unknown. Using the LIKE operator along with a character pattern (search string) you can easily find the match. The character pattern is constructed with the help of two special characters: % and _. The percent character (%) represents zero or more characters, while the underscore character (_) represents just one. The first example below searches all employees starting with the letter ‘A’. The second statement displays a list of all employees who do not contain ‘a’ within their names. The third example searches for employees whose first name has an ‘a’ as the second letter.

NOTE

Some DBMS are case sensitive, therefore you must take care of it while using the LIKE operator. For example, such DBMS would treat 'adam' and 'Adam' differently. Microsoft Access uses * instead of % and ? instead of _.

    SQL Statement :

SELECT       first_name

FROM          employees

WHERE      first_name LIKE ‘A%’;

    Output :

    SQL Statement :

SELECT       first_name

FROM          employees

WHERE       first_name NOT LIKE ‘%a%’;

    Output :

    SQL Statement :

SELECT       first_name

FROM          employees

WHERE       first_name LIKE ‘_a%’;

    Output :

Using the IN Comparison Operator in SQL

Suppose, you wish to see a list of departments under two different locations i.e. 1800 and 2700. If you use the BETWEEN operator, you’ll get a list of departments (including those falling between the two ranges) that you don’t intend to see. Another alternate will be to use a list of conditions like: location_id=1800 OR location_id=2700. Although it is a valid condition, but what if you add ten or more locations to your WHERE clause? Obviously, the statement will grow up in size and you won’t like typing such long statements that consequently enhances program code. To cope with the situation, you’re provided with the IN operator, where you just provide a list of desired values in parentheses, as shown in the following example. Since location_id is a numeric field, you provided the values without the quotation marks. Note that only characters and dates used in the IN list are enclosed within single quotes.

NOTE

The use of the IN operator is a cleaner way if you are working with long lists of conditions. Secondly, the IN operator can also contain another SELECT statement to form a more dynamic WHERE clause.

    SQL Statement :

SELECT       department_id, department_name, location_id

FROM          departments

WHERE       location_id IN (1800,2700);

    Output :

Using the BETWEEN Comparison Operator in SQL

The BETWEEN operator is used in situations where you are searching records between, and inclusive of, a range. You provide a lower value just after the BETWEEN keyword, and put the higher value after the AND logical operator as demonstrated in the following example, where you are trying to fetch employees records whose join date is between 01-JAN-06 and 31-JAN-06, inclusive. Note that date values are also enclosed in single quotation marks and are defined in the default format as ‘DD-MON-YY’. 

    SQL Statement :

SELECT       first_name, last_name, hire_date

FROM          employees

WHERE      hire_date BETWEEN '01-JAN-06' AND '31-JAN-06';

    Output :

Comparing Character String in SQL WHERE Clause

To compare a character string in a WHERE clause, you have to enclose the string in single quotation marks (‘ ‘). The following example searches an employee whose first name is JOHN. When you execute the statement, no rows will be returned, because character strings are case sensitive and should be entered according to the data stored in the table. Since the first name of the searched employee is saved as John in the database, changing the character string from JOHN to John will fetch the match, as shown in the output screenshot.

Alternatively, you can use the UPPER built-in function to match the provided value, like this: WHERE UPPER(first_name)=’JOHN’. In this condition, the UPPER function is used to first convert the column value to upper case before matching it with the provided value. 

    SQL Statement :

SELECT        first_name, last_name, salary  

FROM           employees                             

WHERE       first_name=’JOHN’;                

    Output :

Filtering Data With The WHERE Clause In SQL

All the SELECT statements used in the previous examples were issued to retrieve all rows (records) from the defined tables. To limit the number of returned rows from the query, you use the WHERE clause which should be used immediately after the FROM clause. In this clause you specify a condition comprising three components: expression, comparison operator, and value.

    Syntax :

… WHERE  expression   comparison operator   value         

Here, expression can be a table column, a constant value, or an expression itself. A condition is evaluated by comparing the data defined in the expression position with the value using the comparison operator. In subsequent exercises you will go through different flavors of the WHERE clause. But here, you are provided with couple of simple examples to show some basic usage of this clause.

In the first statement below, LOCATION_ID (a column name) performs as an expression, (=) is the comparison operator, and 1700 is the value which is being compared with the expression. The query retrieves all records (with all columns - *) for the departments established under location number 1700.

In the second example, we used the BETWEEN operator and specified a range of values to get a list of employees who are earning between 100 and 10000.

    SQL Statements :

SELECT         *

FROM           departments

WHERE        location_id = 1700;

SELECT         *

FROM            employees

WHERE        salary between 100 and 10000        

ORDER  BY  salary;

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.