Deprecated: Methods with the same name as their class will not be constructors in a future version of PHP; SEOFriendlyImages has a deprecated constructor in /home/tutorig3/public_html/wp-content/plugins/seo-image/seo-friendly-images.class.php on line 2

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/tutorig3/public_html/wp-content/themes/mts_interactive/options/php-po/php-po.php on line 185
Different Operators in SQL With Example - Tutorialwing

Different Operators in SQL With Example

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.