MySQL WHERE Clause

-- Learning not just skills, but dreams!

Home HTML JAVASCRIPT CSS VUE REACT PYTHON3 JAVA C C++ C# AI GO SQL LINUX VS CODE BOOTSTRAP GIT Local Bookmarks

MySQL Tutorial

MySQL Installation

MySQL Management

MySQL Connection

MySQL Create Database

MySQL Drop Database

MySQL Select Database

MySQL Data Types

MySQL Create Table

MySQL Drop Table

MySQL Insert Data

MySQL Query Data

MySQL WHERE Clause

MySQL UPDATE

MySQL DELETE Statement

MySQL LIKE Clause

MySQL UNION

MySQL ORDER BY Statement

MySQL Grouping

MySQL Joins

MySQL NULL Value Handling

MySQL Regular Expressions

MySQL Transactions

MySQL ALTER Command

MySQL Indexes

MySQL Temporary Tables

MySQL Copying Tables

MySQL Metadata

MySQL Sequences

MySQL Handling Duplicates

MySQL and SQL Injection

MySQL Export Data

MySQL Import Data

MySQL Functions

MySQL Operators

MySQL Command Reference

MySQL Quiz

MySQL Programming Languages

MySQL Node.js

MySQL PHP

MySQL Python

MySQL Java

MySQL Query Data

MySQL UPDATE

Explore Further

Web Services

Web Service

Development Tools

Programming Languages

Scripting

Scripting Languages

Programming

Software

Web Design & Development

Computer Science

MySQL WHERE Clause

We know that we use the SELECT statement to read data from a MySQL table.

To select data conditionally, we can add a WHERE clause to the SELECT statement.

The WHERE clause is used in MySQL to filter query results, returning only rows that meet specific conditions.

Syntax

Here is the general syntax for using the WHERE clause in an SQL SELECT statement to read data from a table:

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

Parameter Explanation:

column1, column2, ... are the names of the columns you want to select. Use * to select all columns.
table_name is the name of the table from which you want to query data.
WHERE condition is the clause used to specify the filtering condition.

More Explanation:

In a query, you can use one or more tables, separated by commas (,), and use the WHERE clause to set query conditions.
You can specify any condition in the WHERE clause.
You can use AND or OR to specify one or more conditions.
The WHERE clause can also be used with SQL DELETE or UPDATE commands.
The WHERE clause is similar to the if condition in programming languages, reading specified data based on field values in the MySQL table.

The following is a list of operators that can be used in the WHERE clause.

The examples in the table below assume A is 10 and B is 20.

Operator	Description	Example
=	Equal to. Checks if two values are equal. Returns true if they are equal.	`(A = B)` returns false.
`<>`, `!=`	Not equal to. Checks if two values are not equal. Returns true if they are not equal.	`(A != B)` returns true.
>	Greater than. Checks if the value on the left is greater than the value on the right. Returns true if the left value is greater.	`(A > B)` returns false.
<	Less than. Checks if the value on the left is less than the value on the right. Returns true if the left value is less.	`(A < B)` returns true.
>=	Greater than or equal to. Checks if the value on the left is greater than or equal to the value on the right. Returns true if the left value is greater or equal.	`(A >= B)` returns false.
<=	Less than or equal to. Checks if the value on the left is less than or equal to the value on the right. Returns true if the left value is less or equal.	`(A <= B)` returns true.

Simple Examples

1. Equal Condition:

SELECT * FROM users WHERE username = 'test';

2. Not Equal Condition:

SELECT * FROM users WHERE username != '';

3. Greater Than Condition:

SELECT * FROM products WHERE price > 50.00;

4. Less Than Condition:

SELECT * FROM orders WHERE order_date < '2023-01-01';

5. Greater Than or Equal Condition:

SELECT * FROM employees WHERE salary >= 50000;

6. Less Than or Equal Condition:

SELECT * FROM students WHERE age <= 21;

7. Combined Conditions (AND, OR):

SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;

SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;

8. Pattern Matching Condition (LIKE):

SELECT * FROM customers WHERE first_name LIKE 'J%';

9. IN Condition:

SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');

10. NOT Condition:

SELECT * FROM products WHERE NOT category = 'Clothing';

11. BETWEEN Condition:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

12. IS NULL Condition:

SELECT * FROM employees WHERE department IS NULL;

13. IS NOT NULL Condition:

SELECT * FROM customers WHERE email IS NOT NULL;

If we want to read specific data from a MySQL table, the WHERE clause is very useful.

Using the primary key as a condition in the WHERE clause is very fast.

If the given condition does not match any records in the table, the query will not return any data.

Reading Data from the Command Prompt

We will use the WHERE clause in the SELECT statement to read data from the MySQL table tutorial_tbl.

The following example will read all records from the tutorial_tbl table where the tutorial_author field value is 'Sanjay':

SQL SELECT WHERE Clause

SELECT * from tutorial_tbl WHERE tutorial_author='';

Output Result:

MySQL's WHERE clause string comparison is case-insensitive. You can use the BINARY keyword to make the WHERE clause string comparison case-sensitive.

Example:

BINARY Keyword

mysql> SELECT * from tutorial_tbl WHERE BINARY tutorial_author='.com';
Empty set (0.01 sec)

mysql> SELECT * from tutorial_tbl WHERE BINARY tutorial_author='.COM';
+-----------+---------------+---------------+-----------------+
| tutorial_id | tutorial_title  | tutorial_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | JAVA Tutorial   | .COM    | 2016-05-06      |
| 4         | Learn Python | .COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

The example uses the BINARY keyword, making the comparison case-sensitive, so the query condition tutorial_author='.com' returns no data.

Reading Data Using PHP Scripts

You can use the PHP function mysqli_query() and the same SQL SELECT command with the WHERE clause to fetch data.

This function is used to execute the SQL command, and then the PHP function mysqli_fetch_array() is used to output all the queried data.

Example

The following example will return records from the tutorial_tbl table where the tutorial_author field value is '.COM':

MySQL WHERE Clause Test:

<?php
$dbhost = 'localhost';  // mysqlServer host address
$dbuser = 'root';            // mysqlUsername
$dbpass = '123456';          // mysqlUsername and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent character encoding issues
mysqli_query($conn , "set names utf8");

// Read tutorial_author where .COM data
$sql = 'SELECT tutorial_id, tutorial_title, 
        tutorial_author, submission_date
        FROM tutorial_tbl
        WHERE tutorial_author=".COM"';

mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2> MySQL WHERE WHERE clause test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['tutorial_id']}</td> ".
         "<td>{$row['tutorial_title']} </td> ".
         "<td>{$row['tutorial_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
// Release memory
mysqli_free_result($retval);
mysqli_close($conn);
?>

Output Result:

MySQL Query Data

MySQL UPDATE

ByteDance Coding Plan

Supports mainstream large models like Doubao, GLM, DeepSeek, Kimi, MiniMax. Official supply, stable and reliable.

Configuration Guide

¥9.9 / Month

Activate Now

iFlytek Star Coding Plan

Includes free model call quota. DeepSeek, GLM, Kimi, MiniMax. One-stop experience and deployment platform.

Configuration Guide

¥3.9 / Month

Activate Now

2 Notes

Write Note

Adatec

238***0500@qq.com

521

where: The commonly used keyword in databases is 'where', used to filter queries in the initial table. It is a constraint declaration used to constrain data, taking effect before returning the result set.

group by: Groups the result set returned by the SELECT query based on a field or expression, obtaining sets of groups, and then takes a specified field or expression value from each group.

having: Used to filter the grouped results from the 'where' and 'group by' queries, retrieving groups that meet the conditions. It is a filter declaration, performing filtering on the query results after they have been returned.

Execution Order:

select –>where –> group by–> having–>order by

Adatec

238***0500@qq.com

7 years ago (2019-07-07)

Marine

695***631@qq.com

Reference Address

612

The execution order above is incorrect. The order from Google is as follows:

FROM, including JOINs

WHERE

GROUP BY

HAVING

WINDOW functions

SELECT

DISTINCT

UNION

ORDER BY

LIMIT and OFFSET

Marine

695***631@qq.com

Reference Address

6 years ago (2020-08-30)

Category Navigation

Python / Data Science

AI / Intelligent Development

Front-end Development

Back-end Development

Databases

Mobile Development

DevOps / Engineering

Programming Languages

Computer Fundamentals

XML / Web Service

.NET

Website Building

MySQL Tutorial

MySQL Installation

MySQL Management

MySQL Connection

MySQL Create Database

MySQL Drop Database

MySQL Select Database

MySQL Data Types

MySQL Create Table

MySQL Drop Table

MySQL Insert Data

MySQL Query Data

MySQL WHERE Clause

MySQL UPDATE

MySQL DELETE Statement

MySQL LIKE Clause

MySQL UNION

MySQL ORDER BY Statement

MySQL Grouping

MySQL Joins

MySQL NULL Value Handling

MySQL Regular Expressions

MySQL Transactions

MySQL ALTER Command

MySQL Indexes

MySQL Temporary Tables

MySQL Copying Tables

MySQL Metadata

MySQL Sequences

MySQL Handling Duplicates

MySQL and SQL Injection

MySQL Export Data

MySQL Import Data

MySQL Functions

MySQL Operators

MySQL Command Reference

MySQL Quiz

MySQL Programming Languages

MySQL Node.js

MySQL PHP

MySQL Python

MySQL Java

Online Examples

·HTML Examples

·CSS Examples

·JavaScript Examples

·Ajax Examples

·jQuery Examples

·XML Examples

·Java Examples

Character Sets & Tools

· HTML Character Set Settings

· HTML ASCII Character Set

· JS Obfuscation/Encryption

· PNG/JPEG Image Compression

· HTML Color Picker

· JSON Formatter Tool

· Random Number Generator

Latest Updates

· AI Ethics and Safety

· How AI Works

· AI Tools Landscape

· Prompt Prompts

· Introduction to AI

· AI Beginner Tutorial

· Large Model Multimodal (M...

Site Information

· Feedback

· Disclaimer

· About Us

· Article Archive

Follow WeChat

My Favorites

Mark Article

Browsing History

Clear All

No records yet

YouTip

Mysql Where Clause

MySQL Tutorial

MySQL Programming Languages

MySQL Query Data

Explore Further

MySQL WHERE Clause

Syntax

Simple Examples

Reading Data from the Command Prompt

SQL SELECT WHERE Clause

BINARY Keyword

Reading Data Using PHP Scripts

Example

MySQL WHERE Clause Test:

MySQL Query Data

ByteDance Coding Plan

iFlytek Star Coding Plan

2 Notes

Category Navigation

MySQL Tutorial

Online Examples

Character Sets & Tools

Latest Updates

Site Information

📂 Categories