WHERE Clause in MySQL | Filtering in MySQL

WHERE clause in MySQL

The WHERE clause is used to apply a particular condition while selecting rows from the table. It helps in filtering the rows according to any particular condition.

Syntax: 

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

In the above-written syntax, there is a condition after the ‘WHERE’ clause. Those rows are selected which fulfill the condition written after the ‘WHERE’ clause. 

The condition is a combination of one or more expressions combined with logical operators like AND, OR, NOT.

For example: 

SELECT customer_id, custome_name, product_id 
FROM customers 
WHERE custome_name='Vivek';

Output:

It can be seen in the above table returned as an output, the only row which has custome_name= 'Vivek' is Selected.

AND Operator in MySQL

The AND operator is used to combine two or more Boolean expressions. It returns true when all of them are true else it returns false. It is used in the WHERE clause to combine multiple expressions.

Syntax: 

SELECT column1, column2,...
FROM table_name  
WHERE condition1 AND condition2,...;

For example: 

SELECT customer_id, customer_name, product_id 
FROM customers 
WHERE price≥3000 AND price≤4000;

Output:

OR Operator in MySQL

The OR operator is used to combine two or more Boolean expressions. It returns true when any of them are true else it returns false. It is used in the WHERE clause to combine multiple expressions.

Syntax: 

SELECT column1, column2, …..
FROM table_name
WHERE condition1 OR condition2, …..;

For example:

SELECT customer_id, customer_name, product_id 
FROM customers WHERE price≥3000 OR customer_name='Ankit';

Output:

All the rows for which satisfies the conditions are selected in the table returned as an output.

IN Operator in MySQL

The IN operator is used to check if a value matches any of the values in a list of values. It is similar to the OR operator as if any of the values in the list matches it returns true.

Syntax: 

SELECT column1, column2, …..
FROM table_name
WHERE column_name IN (value1, value2, ….);

              or

SELECT column1, column2, ….. 
FROM table_name  
WHERE column_name IN (SELECT statement );

For example:

SELECT customer_id, customer_name, product_id 
FROM customers 
WHERE customer_name IN ('Ankit', 'Karan');

Output:

It can be seen in the above table returned as an output, the rows which have a matching value in the list are selected.

NOT IN operator in MySQL

The NOT IN operator in MySQL is just the opposite of IN. If a value doesn't match with any of the values in the list, it returns true.

Syntax: 

SELECT column1, column2, …..
FROM table_name
WHERE column_name NOT IN (value1, value2, ….);

              or

SELECT column1, column2, ….. 
FROM table_name
WHERE column_name NOT IN (SELECT statement );

For example:

SELECT customer_id, customer_name, product_id 
FROM customers 
WHERE customer_name NOT IN ('Ankit', 'Karan');

Output:

It can be seen in the above table returned as an output, only those rows are selected which does not have matching values in the list.

BETWEEN Operator in MySQL

The BETWEEN operator specifies whether a value is in a range or not. It is a logical operator. Suppose if a value is between begin and end values inclusive, it returns true else false.

Syntax: 

SELECT column1, column2, …..
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

For Example:

SELECT customer_id, customer_name, product_id FROM customers WHERE price BETWEEN 1500 AND 3000;

Output:

Only those rows are selected which are in the given range in the query.

LIKE Operator in MySQL

The LIKE operator is used in MySQL to search for a specific pattern in a string. If an expression matches the pattern, it returns true else false.

There are two wildcards in MySQL, used with the LIKE operator for searching a pattern.

• The percentage sign (%). It represents zero or more characters.
• The underscore sign (_). It represents a single character.

Syntax: 

SELECT column1, column2, …..
FROM table_name 
WHERE column_name LIKE pattern;

For example:

SELECT customer_id, customer_name, product_id 
FROM customers 
WHERE customer_name LIKE 'A'%;

Output:

It can be seen in the above table, only the rows in which customer_name starts with 'A' are selected.

SELECT customer_id, customer_name, product_id 
FROM customers 
WHERE customer_name LIKE 'As_i_sh';

Output:

LIMIT Operator in MySQL

The LIMIT clause is used in MySQL to determine the number of rows to return. It accepts one or two arguments.

Syntax: 

SELECT column1, column2, …..                                                                                       
FROM table_name                                                                                                           
LIMIT offset, row_count;

In the above syntax, offset represents the starting row. If offset is ‘0’ it represents 1st row. The row_count specifies the number of rows to be returned.

For example: 

SELECT customer_id, customer_name, product_id 
FROM customers LIMIT 1,3;

The above query will select 3 rows from the 2nd row as the offset is 1.

Output:

SELECT customer_id, customer_name, product_id 
FROM customers LIMIT 3;

It will return just 3 rows from the top as it has no offset.

Output:

IS NULL Operator in MySQL

The IS NULL is used to check if a value is NULL or not. If the value is NULL, it returns true else false.

Syntax: 

SELECT column1, column2, ….. 
FROM table_name                                                                                                           
WHERE column_name IS NULL;

Note: IS NOT NULL is the negation of IS NULL.