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:

---

Equal to Operator with WHERE query

EXAMPLE-

SELECT * FROM mobiles WHERE price=14000;

Query result-

---

Greater than (>) Operator with WHERE query

EXAMPLE-

SELECT * FROM mobiles WHERE price>18000;

Query result-

---

Less than (<) Operator with WHERE query

SELECT * FROM mobiles WHERE price<13000;

Query result-

---

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

SELECT * FROM mobiles WHERE price>=28000;

Query result-

---

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-

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-

---

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-

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-

Or

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

Query result-

---

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-