SQL: WHERE clause

WHERE clause is used to filter the result set. You can specify the condition in WHERE clause while submitting the query, and SQL server return the results that satisfy the condition.

 

Syntax

WHERE condition

 

Let’s try with following person table.

 

CREATE TABLE person (

    id int AUTO_INCREMENT,

    name varchar(20),

    city varchar(25),

    country varchar(25),

    PRIMARY KEY(id)

);

 

Insert following data to person table.

 

INSERT INTO person(id, name, city, country) VALUES (1, 'Krishna', 'Bangalore', 'India');

INSERT INTO person(id, name, city, country) VALUES (2, 'Ram', 'Delhi', 'India');

INSERT INTO person(id, name, city, country) VALUES (3, 'Chamu', 'Berlin', 'Germany');

INSERT INTO person(id, name, city, country) VALUES (4, 'Rama Devi', 'London', 'England');

INSERT INTO person(id, name, city, country) VALUES (5, 'Lakshman', 'Bangalore', 'India');

 

a. Get all the data from person table

mysql> SELECT * FROM person;
+----+-----------+-----------+---------+
| id | name      | city      | country |
+----+-----------+-----------+---------+
|  1 | Krishna   | Bangalore | India   |
|  2 | Ram       | Delhi     | India   |
|  3 | Chamu     | Berlin    | Germany |
|  4 | Rama Devi | London    | England |
|  5 | Lakshman  | Bangalore | India   |
+----+-----------+-----------+---------+
5 rows in set (0.00 sec)

 

b. Get all the persons who are from the city Bangalore.

mysql> SELECT * FROM person p WHERE p.city='Bangalore';
+----+----------+-----------+---------+
| id | name     | city      | country |
+----+----------+-----------+---------+
|  1 | Krishna  | Bangalore | India   |
|  5 | Lakshman | Bangalore | India   |
+----+----------+-----------+---------+
2 rows in set (0.00 sec)

 

c. Get all the person who are not from the city Bangalore.

mysql> SELECT * FROM person p WHERE p.city!='Bangalore';
+----+-----------+--------+---------+
| id | name      | city   | country |
+----+-----------+--------+---------+
|  2 | Ram       | Delhi  | India   |
|  3 | Chamu     | Berlin | Germany |
|  4 | Rama Devi | London | England |
+----+-----------+--------+---------+
3 rows in set (0.00 sec)

d. Get all the persons whose id is > 2 and from the city Bangalore.

mysql> SELECT * FROM person p WHERE p.city='Bangalore' AND p.id > 2;
+----+----------+-----------+---------+
| id | name     | city      | country |
+----+----------+-----------+---------+
|  5 | Lakshman | Bangalore | India   |
+----+----------+-----------+---------+
1 row in set (0.00 sec)

e. Get all the person who are from city ‘London’ OR from the country India.

mysql> SELECT * FROM person p WHERE p.city='London' OR p.country='India';
+----+-----------+-----------+---------+
| id | name      | city      | country |
+----+-----------+-----------+---------+
|  1 | Krishna   | Bangalore | India   |
|  2 | Ram       | Delhi     | India   |
|  4 | Rama Devi | London    | England |
|  5 | Lakshman  | Bangalore | India   |
+----+-----------+-----------+---------+
4 rows in set (0.00 sec)

 

 

 

 

Previous                                                    Next                                                    Home