In this article, we will be discussing SELECT with WHERE clause and how to use it in different situations mainly,
- Syntax
- MySQL SELECT WHERE with EQUALS and NOT EQUALS
- MySQL SELECT WHERE with IN and NOT IN
- MySQL SELECT WHERE with RANGE
- MySQL SELECT WHERE with COMPARISON OPERATORS
- MySQL SELECT WHERE with NULL and NOT NULL
- MySQL SELECT WHERE with LIKE
- MySQL SELECT WHERE with multiple conditions using AND, OR
WHERE clause is used when we want to add a specific condition into our SELECT statement, let us get started by looking into the snapshot of sample table student_enroll_data to be used across the examples.
The image_1 above shows that there are total 12 rows in the table.
Syntax:-
SELECT columnList/* FROM tableName WHERE condition1 {[AND/ OR] condition2 ...}
- columnList/* – we can select one or many columns or all columns of the table (or a query) using *.
- tableName – name of the table from where columns are selected.
- condition1 – condition to be specified after the where clause.
- [AND/ OR] condition2 – we can add multiple conditions using AND, OR keywords.
- WHERE clause will be calculated after FROM but before SELECT clause.
MySQL SELECT WHERE with EQUALS and NOT EQUALS
MySQL SELECT WHERE with EQUALS:-
SELECT * FROM student_enroll_data WHERE student_name = "Veronica";
Action Output Message:-
Frequently Asked:
3 row(s) returned.
Output:-
MySQL SELECT WHERE with NOT EQUALS:-
SELECT * FROM student_enroll_data WHERE student_name != "Veronica";
Action Output Message:-
9 row(s) returned.
Output:-
MySQL SELECT WHERE with IN and NOT IN
MySQL SELECT WHERE with IN:-
SELECT * FROM student_enroll_data WHERE student_id IN (1,4,26);
Action Output Message:-
3 row(s) returned.
Output:-
MySQL SELECT WHERE with NOT IN:-
SELECT * FROM student_enroll_data WHERE student_id NOT IN (1,4,26);
Action Output Message:-
9 row(s) returned.
Output:-
MySQL SELECT WHERE with RANGE
MySQL SELECT WHERE with RANGE:-
SELECT * FROM student_enroll_data WHERE student_id BETWEEN 1 AND 10;
Action Output Message:-
7 row(s) returned.
Output:-
MySQL SELECT WHERE with IS NULL and IS NOT NULL
MySQL SELECT WHERE with IS NULL:-
SELECT * FROM student_enroll_data WHERE fee_submitted IS NULL;
Action Output Message:-
1 row(s) returned.
Output:-
MySQL SELECT WHERE with IS NOT NULL:-
SELECT * FROM student_enroll_data WHERE fee_submitted IS NOT NULL;
Action Output Message:-
11 row(s) returned.
Output:-
MySQL SELECT WHERE with COMPARISON OPERATORS
We can use multiple comparison operators along with WHERE in a MySQL SELECT statement. Below are the available options.
- < : Less Than
- <= : Less Than Equal To
- > : Greater Than
- >= : Greater Than Equal To
- = : Equal To
- != : Not Equal To
MySQL SELECT WHERE with Greater Than Equal To:-
SELECT * FROM student_enroll_data WHERE enroll_date >= '2021-06-06';
Action Output Message:-
10 row(s) returned.
Output:-
MySQL SELECT WHERE with LIKE
MySQL SELECT with LIKE:-
SELECT * FROM student_enroll_data WHERE student_name LIKE '%on%';
Action Output Message:-
3 row(s) returned.
Output:-
The output in image_10 shows that only three rows are returned which have ‘on’ somewhere in the student_name.
MySQL SELECT WHERE with AND , OR
We can add additional conditions into the WHERE clause using the AND, OR keywords. AND keyword is used when we want to add conditions such that if both the conditions are true, the query will retrieve the results. OR keyword is used when we want to add conditions such that if any one of the conditions is true, the query will retrieve the results.
MySQL SELECT WHERE with AND
SELECT * FROM student_enroll_data WHERE student_name = "Veronica" AND student_id <=10 ;
Action Output Message:-
2 row(s) returned.
Output:-
MySQL SELECT WHERE with OR
SELECT * FROM student_enroll_data WHERE student_name = "Veronica" OR student_id <=10 ;
Action Output Message:-
8 row(s) returned.
Output:-
READ MORE
We hope this article helped you to understand MySQL SELECT WHERE. Good Luck!!!