Use of SQL Operators with WHERE in SQL

in SQL, we can use operators with WHERE query so that we can display/filter data from tables according to our thinking.

There are so many operators to use with WHERE (conditional clause) in SQL which are given below.

Operators for “where” query

= (Equal to operator in SQL but in JS assignment operator)
> (Greater than)
< (Less than)
>= (Greater than or equal to)
<= (Less than or equal to)
<> (Not equal to)
BETWEEN
IN()
AND
OR
NOT

To understand all these with examples, let’s start from the beginning.

Make a database by running query

CREATE DATABASE jai_school;

Create a table in the database

CREATE TABLE mobiles(brand_name VARCHAR(30),price INT(10));

Insert data in the table

INSERT INTO mobiles(brand_name,price)VALUES("Samsung",80000);
INSERT INTO mobiles(brand_name,price)VALUES("Apple",140000);
INSERT INTO mobiles(brand_name,price)VALUES("Nokia",25000);
INSERT INTO mobiles(brand_name,price)VALUES("MI",14000);
INSERT INTO mobiles(brand_name,price)VALUES("Honor",9000);
INSERT INTO mobiles(brand_name,price)VALUES("Sony",18000);
INSERT INTO mobiles(brand_name,price)VALUES("Realme",14000);
INSERT INTO mobiles(brand_name,price)VALUES("Oneplus",28000);
INSERT INTO mobiles(brand_name,price)VALUES("Motorola",13000);

Table Image:

mysql table image

Equal to Operator with WHERE query

EXAMPLE-

SELECT * FROM mobiles WHERE price=14000;

Query result

equal-to-operator-with-where

Greater than (>) Operator with WHERE query

EXAMPLE-

SELECT * FROM mobiles WHERE price>18000;

Query result

greater-than-operator-with-where

Less than (<) Operator with WHERE query

SELECT * FROM mobiles WHERE price<13000;

Query result

less than operator with where

Greater than or equal to (>=) Operator with WHERE query

SELECT * FROM mobiles WHERE price>=28000;

Query result

greater than equal to operator with where

Less than or equal to (<=) Operator with WHERE query

SELECT * FROM mobiles WHERE price<=14000;

Query result


Not equal to Operator with WHERE query

SELECT * FROM mobiles WHERE price<>14000;

Or

SELECT * FROM mobiles WHERE price != 14000;

Query result

Not equal to Operator with WHERE query

IBM DB2 and Microsoft access support only this <> (not equal to) operator.


BETWEEN with WHERE query

SELECT * FROM mobiles WHERE price BETWEEN 20000 AND 90000;

Or

SELECT * FROM mobiles WHERE price >= 20000 AND price <= 90000;

Query result

between-and-operator-with-where

IN() with WHERE query

SELECT * FROM mobiles WHERE price IN(14000,28000);

Or

SELECT * FROM mobiles WHERE price = 14000 OR price = 28000;

Query result

If column field data is text instead of number then put single quote to qualify it.

SELECT * FROM mobiles WHERE brand_name IN('Apple','Sony');

AND operator

SELECT * FROM mobiles WHERE price>14000 AND brand_name LIKE "s%";

Query result

AND and LIKE operator with where

Read more about LIKE


OR operator

SELECT * FROM mobiles WHERE price=25000 OR price=6000;

Query result


NOT operator

SELECT * FROM mobiles WHERE NOT price=14000;

Query result

not operator with where

Or

SELECT * FROM mobiles WHERE price NOT IN(14000,28000);

Query result

NOT and IN() with where

WHERE query with % (modulus) operator

Modulus operator is similar to / (division operator) but it returns the remainder. Remainder of 0 means there is not remainder. For example, if we divide 12 by 4 then remainder will be 0 because 12 is completely divided by 4 but in another case when we divide 13 by 4 then remainder will not be 0 (It will be 1) because 13 is not divided by 4.

SELECT * FROM mobiles WHERE price%10000 = 0;

Query result-

where with modulus operator

Leave a Comment