Different Operators in SQL With Example

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

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

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

fname Iname
Alok Pawar
Ashwini Kakde
Abhishek Agarwal

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

fname Iname
Neelam Padma
Sharada Jadhao
Sharada Patil

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

fname Iname salary address
Neelam Padma 25000.00 Sambhaji nagar, Pune-14
Alok Pawar 15000.00 Pimpri, Pune-422343

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

fname Iname salary address
Sharada Jadhao 35000.00 Vikas nagar, Nagpur-15
Vaishnavi Munigial 10000.00 Udyog wasti,
Solapur-42

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

fname Iname salary
sharada Jadhao 35000.00
Abhishek Agarwal 40000.00
Sharada Patil 90000.00

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

fname Iname salary
Neelam Padma 25000.00
Alok Pawar 15000.00
Vaishnavi Munigial 10000.00

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.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners