In this article, we will be discussing SELECT with WHERE clause and how to use it in different situations mainly,

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.

image_1: student_enroll_data

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:-

3 row(s) returned.

Output:-

image_2

MySQL SELECT WHERE with NOT EQUALS:-

SELECT * FROM student_enroll_data WHERE student_name != "Veronica";

Action Output Message:-

9 row(s) returned.

Output:-

image_3

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:-

image_4

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:-

image_5

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:-

image_6

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:-

image_7

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:-

image_8

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:-

image_9

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:-

image_10

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 keywordsAND 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:-

image_11

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:-

image_12

READ MORE

We hope this article helped you to understand MySQL SELECT WHERE. Good Luck!!!