This tutorial will explain how to use WHERE clause within a SQL query and how to use them with the examples.
WHERE clause is used to filter the resultset.
This is the general statement to retrieve n number of records from a table
SELECT
column1, column2, column3
FROM
tablename
WHERE
filterconditions;
Lets create a sample table and add some rows
CREATE TABLE SampleContacts (
FullName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Age INT NOT NULL
);
INSERT INTO SampleContacts
(FullName, Gender, Age)
VALUES
('Contact1', 'M', 26),
('Contact2', 'M', 29),
('Contact3', 'F', 27),
('Contact4', 'F', 28),
('Contact5', 'F', 25),
('Contact6', 'M', 24)
Products Table
A) Filter table by single column
The following statement retrieves contacts with the Gender equals to 'M'
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
Gender = 'M'
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact2 M 29
Contact6 M 24
B) Filter table by multiple columns
The following statement retrieves contacts with the Gender equals to 'M' and Age equals to 26
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
Gender = 'M' AND Age = 26
| FullName | Gender | Age |
---------------------------
Contact1 M 26
C) Filter table by comparison filters
The following statement retrieves contacts with the Gender equals to 'M' and Age less than 27
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
Gender = 'M' AND Age <27
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact6 M 24
D) Filter table by using OR clause on filters
The following statement retrieves contacts with the Gender equals to 'M' or Age less than 27.
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
Gender = 'M' OR Age <27
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact2 M 29
Contact5 F 25
Contact6 M 24
E) Filter table by using between clause
The following statement retrieves contacts with the Age from 25 to 27.
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
AGE BETWEEN 25 AND 27
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact3 F 27
Contact5 F 25
F) Filter table by using IN operator
The following statement retrieves contacts within the following Age values 24, 26, 29.
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
AGE IN(24, 26, 29)
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact2 M 29
Contact6 M 24
G) Filter table by using LIKE operator
The following statement retrieves contacts within the following FullName contains act3 string.
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE
FullName LIKE '%act3%'
| FullName | Gender | Age |
---------------------------
Contact3 F 27