In this post, we will learn about different operators in SQL. We will learn about below operators –

• LIKE Operator
• BETWEEN Operator
• IN Operator
• AND Operator
• OR Operator

1. SQL – LIKE operator

The LIKE predicate allows comparison of one string value with another string value. This is achieved by using wildcard characters:

• % allows to match any string of any length (including zero length)
• _(underscore) allows to match on a single character

LIKE predicate is not case sensitive so ‘b%’ and ‘B%’ are the same.

Example 1

Display records where fname starts with ‘A’

mysql> SELECT fname, lname from employee where fname LIKE 'A%';

Result

3 rows in set (0.00 sec)

Example 2

Display records where fname ends with ‘A’

mysql> SELECT fname, lname from employee where fname LIKE '%A'

Result

3 rows in set (0.00 sec)

Example 3

Display records where address is in ‘Pune’

mysql> SELECT fname, lname, salary, address from employee where address like '%Pune%'

Result

2 rows in set (0.00 sec)

Example 4

Display records where address is not in ‘Pune’

mysql> SELECT fname, lname, salary, address from employee where address not like '%Pune%'

Result

2 rows in set (0.00 sec)

2. SQL – BETWEEN operator

BETWEEN allows you to retrieve values within a specific range.

Example 1

Display only those employees whose salary is in between 30000 to 100000.

mysql> SELECT fname, lname, salary FROM employee WHERE salary BETWEEN 30000 AND 100000;

Result

3 rows in set (0.00 sec)

Example 2

Display employees whose salary is not in range 30000to 100000. For this purpose NOT BETWEEN is used.

mysql> SELECT fname, lname FROM employee WHERE salary NOT BETWEEN 30000 AND 100000;

Result

3 rows in set (0.00 sec)

3. IN operator

The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values then the IN predicate is used. The IN predicate helps to reduce the need to use multiple OR conditions.

Example 1

Use of “=”operator and “IN” operator

mysql> SELECT name, city FROM student WHERE city= 'Pune' OR city = 'Mumbai'

OR

mysql> SELECT name, city FROM student WHERE city IN ('Pune', 'Mumbai')

Example 2

Suppose if we want to find out names which do not locate in Pune and Mumbai

mysql> SELECT name, city FROM student WHERE city NOT IN (‘Pune’, ‘Mumbai’)

4. SQL – AND operator

AND operator displays the record if all the conditions separated by AND are true.

5. SQL – OR operator

OR operator displays the record if any conditions separated by OR are true.

That’s end of our post on different operators in SQL.