SQL Where Clause

In this lesson, you will learn how to select specific records from a database table using SQL.

The WHERE Clause In SQL

The WHERE clause is used to set conditions.

So you can use it to specify which records you want to select, update or delete.

Syntax

The WHERE clause can be used with the SELECT statement as folows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

You learn how to use it to update or delete records in the upcoming lessons.

WHERE Clause Operators

The following operators can be used in the WHERE clause to set the condition.

Operator	Description
`=`	Equal operator Check if two values are equal. Eg: `WHERE id = 5`
`!=`	Not equal operator Check if two values are not equal. Eg: `WHERE tax != 10`
`<>`	Not equal operator Check if two values are not equal; same as != operator. Eg: `WHERE tax <> 10`
`>`	Greater than operator Check if the value is greater than the other value. Eg: `WHERE age > 18`
`>=`	Greater than or equal operator Check if the value is greater or equal to the other value. Eg: `WHERE age >= 18`
`<`	Less than operator Check if the value is less than the other value. Eg: `WHERE age < 64`
`<=`	Less than or equal operator Check if the value is less than the other value. Eg: `WHERE age <= 64`
`IN`	In operator Check if a value matches any value in a list or subquery. Eg: `WHERE city IN ('London', 'Beirut')`
`BETWEEN`	BETWEEN operator Check if a value is between two values. Eg: `WHERE age BETWEEN age >= 18 AND age <= 64`
`LIKE`	Like operator Check if the value matches specifc pattern. Eg: `WHERE country LIKE 'E%'`

When writting a string pattern for the LIKE operator you can use the following wildcard characters:

% — The percent sign matches any number of characters, even zero characters.
_ — The underscore matches exactly one character.

In MySQL, SQL Server, Oracle and SQLite we use % and _ as wildcard characters.
In Access, we use * and ? instead of them.

Preparing A Database

Since you will learn how to set condition when selecting data from tables, you must have a database table with some records to practice retrieving them. Execute the following query to create the database.

Example

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "users" inside the selected database
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    country VARCHAR(100),
    birthdate Date,
    revenue DECIMAL(7,2)
);

-- Insert 10 records in the "users" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO users VALUES (null, "Emily", "United Kigndom", "1990-10-23", 540);
INSERT INTO users VALUES (null, "Omar", "Jordan", "1992-05-08", 745);
INSERT INTO users VALUES (null, "Olivia", "France", "1988-07-12", 376);
INSERT INTO users VALUES (null, "William", "Norway", "1994-12-04", 845);
INSERT INTO users VALUES (null, "Sophia", "Hawai", "1991-10-27", 515);
INSERT INTO users VALUES (null, "Daniel", "Germany", "1993-11-12", 1830);
INSERT INTO users VALUES (null, "Ava", "Japan", "1995-03-10", 921);
INSERT INTO users VALUES (null, "James", "Australia", "1990-02-14", 713);
INSERT INTO users VALUES (null, "Mia", "France", "1991-08-12", 490);
INSERT INTO users VALUES (null, "Ethan", "Russia", "1985-09-20", 880);

SQL Operators Examples

The following query returns all data for the user who has an id equal to 5.

Example 1

SELECT * FROM users
WHERE id = 5;

Output

id	username	country	birthdate	revenue
5	Sophia	Hawai	1991-10-27	515.00

The following query returns all data for users who are not from France.
Tip: That means the country should not be equal to France.

Example 2

SELECT * FROM users
WHERE country <> 'France';

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
2	Omar	Jordan	1992-05-08	745.00
4	William	Norway	1994-12-04	845.00
5	Sophia	Hawai	1991-10-27	515.00
6	Daniel	Germany	1993-11-12	1830.00
7	Ava	Japan	1995-03-10	921.00
8	James	Australia	1990-02-14	713.00
10	Ethan	Russia	1985-09-20	880.00

The following query returns all data for users who have revenue greater than 700 dollar.

Example 3

SELECT * FROM users
WHERE revenue > 700;

Output

id	username	country	birthdate	revenue
2	Omar	Jordan	1992-05-08	745.00
4	William	Norway	1994-12-04	845.00
6	Daniel	Germany	1993-11-12	1830.00
7	Ava	Japan	1995-03-10	921.00
8	James	Australia	1990-02-14	713.00
10	Ethan	Russia	1985-09-20	880.00

The following query returns all data for users who were born in 1992 or later.
Tip: That means the birthdate should be greater than or equal to 1992-01-01.

Example 4

SELECT * FROM users
WHERE birthdate >= '1992-01-01';

Output

id	username	country	birthdate	revenue
2	Omar	Jordan	1992-05-08	745.00
4	William	Norway	1994-12-04	845.00
6	Daniel	Germany	1993-11-12	1830.00
7	Ava	Japan	1995-03-10	921.00

The following query returns all data for users who have revenue less than 600 dollar.

Example 5

SELECT * FROM users
WHERE revenue < 600;

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
3	Olivia	France	1988-07-12	376.00
5	Sophia	Hawai	1991-10-27	515.00
9	Mia	France	1991-08-12	490.00

The following query returns all data for users who were born in 1990 or before.
Tip: That means the birth date should be less than or equal to 1990-12-31.

Example 6

SELECT * FROM users
WHERE birthdate <= '1990-12-31';

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
3	Olivia	France	1988-07-12	376.00
8	James	Australia	1990-02-14	713.00
10	Ethan	Russia	1985-09-20	880.00

The following query returns all data for users who are from Germany, Russia or France.

Example 7

SELECT * FROM users
WHERE country IN ('Germany', 'Russia', 'France');

Same query can be written as follows.

SELECT * FROM users
WHERE country = 'Germany' OR country = 'Russia' OR country = 'France';

Output

id	username	country	birthdate	revenue
3	Olivia	France	1988-07-12	376.00
6	Daniel	Germany	1993-11-12	1830.00
9	Mia	France	1991-08-12	490.00
10	Ethan	Russia	1985-09-20	880.00

The following query returns all data for users who have revenue between 500 and 800 dollar.

Example 8

SELECT * FROM users
WHERE revenue BETWEEN 500 AND 800;

Same query can be written as follows.

SELECT * FROM users
WHERE revenue >= 500 AND revenue <= 800;

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
2	Omar	Jordan	1992-05-08	745.00
5	Sophia	Hawai	1991-10-27	515.00
8	James	Australia	1990-02-14	713.00

The following query returns all data for users who were born from 1990 to 1992.
Tip: the birthdate should be from the first day in 1990 to the last day in 1992.

Example 9

SELECT * FROM users
WHERE birthdate BETWEEN '1990-01-01' AND '1992-12-31';

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
2	Omar	Jordan	1992-05-08	745.00
5	Sophia	Hawai	1991-10-27	515.00
8	James	Australia	1990-02-14	713.00
9	Mia	France	1991-08-12	490.00

The following query returns all data for users who have a username start the letter 'E'.

Example 10

SELECT * FROM users
WHERE username LIKE 'E%';

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
10	Ethan	Russia	1985-09-20	880.00

The following query returns all data for users who have the letter 'm' as the second letter in their username.

Example 10

SELECT * FROM users
WHERE username LIKE '_m%';

Output

id	username	country	birthdate	revenue
1	Emily	United Kigndom	1990-10-23	540.00
2	Omar	Jordan	1992-05-08	745.00

The following query returns all data for users who have a username starting with 'J' and ending with 's'.

Example 11

SELECT * FROM users
WHERE username LIKE 'J%s';

Output

id	username	country	birthdate	revenue
8	James	Australia	1990-02-14	713.00

WHERE Clause Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "employees" and check whether it exists or not. the table should contain the following fields:

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
first_name VARCHAR(50)
last_name VARCHAR(50)
hire_date Date

3- Write a query that inserts 5 rows in the "employees" table.

4- Write a query that selects all employees that are hired in 2020.
Note: The returned column should look like ID, Name (which is first_name and last_name), Hire Date.

Solution

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS employees (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date Date
);

INSERT INTO employees (first_name, last_name, hire_date) VALUES
('Jack', 'Reed', '2018-05-22'),
('Emma', 'Clark', '2020-02-12'),
('Luke', 'Lee', '2020-10-05'),
('Jim', 'Wilson', '2021-03-10'),
('Lily', 'Brooks', '2022-01-25');

SELECT
    id AS 'ID',
    CONCAT(first_name, ' ', last_name) AS 'Full Name',
    hire_date AS 'Hire Date'
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

Next Lesson» »Previous Lesson

SQL Where Clause

The WHERE Clause In SQL

Syntax

WHERE Clause Operators

Preparing A Database

Example

SQL Operators Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Example 9

Example 10

Example 10

Example 11

WHERE Clause Exercise

Solution

Our Mission

Important Links

Copyright

Tutorials

Online Tools

Sections

Search