MySQL SELECT Statement | MySQL WHERE Clause

If you are interested to learn about the MySQL SQL

The MySQL SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

For example: SELECT order_id, quantity, unit_price FROM order_details WHERE quantity < 500 ORDER BY quantity ASC, unit_price DESC; This MySQL SELECT example would return only the order_id, quantity, and unit_price fields from the order_details table where the quantity is less than 500.

How do I write a select statement in MySQL?

First, specify one or more columns from which you want to select data after the SELECT keyword. If the select_list has multiple columns, you need to separate them by a comma ( , ). Second, specify the name of the table from which you want to select data after the FROM keyword.

Demo Database

In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Customers” table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

SELECT Columns Example

The following SQL statement selects the “CustomerName”, “City”, and “Country” columns from the “Customers” table:

Example

SELECT CustomerName, City, Country FROM Customers;

SELECT * Example

The following SQL statement selects ALL the columns from the “Customers” table:

Example

SELECT * FROM Customers;

The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the “Country” column in the “Customers” table:

Example

SELECT Country FROM Customers;

Now, let us use the SELECT DISTINCT statement and see the result.

SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the “Country” column in the “Customers” table:

Example

SELECT DISTINCT Country FROM Customers;

The following SQL statement counts and returns the number of different (distinct) countries in the “Customers” table:

Example

SELECT COUNT(DISTINCT Country) FROM Customers;

The MySQL WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

WHERE Syntax

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

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

WHERE Clause Example

The following SQL statement selects all the customers from “Mexico”:

Example

SELECT * FROM Customers
WHERE Country = 'Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:

Example

SELECT * FROM Customers
WHERE CustomerID = 1;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

OperatorDescriptionExample
=EqualTry it
>Greater thanTry it
<Less thanTry it
>=Greater than or equalTry it
<=Less than or equalTry it
<>Not equal. Note: In some versions of SQL this operator may be written as !=Try it
BETWEENBetween a certain rangeTry it
LIKESearch for a patternTry it
INTo specify multiple possible values for a column

Fetching Data from the Command Prompt

Example

The following example will return all the records from the tutorials_tbl table for which the author name is Sanjay.

root@host# mysql -u root -p password;
Enter password:*******
mysql&gt; use TUTORIALS;
Database changed
mysql&gt; SELECT * from tutorials_tbl WHERE tutorial_author = 'Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-21   |      
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql&gt;

Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive by using the BINARY keyword as follows −

root@host# mysql -u root -p password;
Enter password:*******
mysql&gt; use TUTORIALS;
Database changed
mysql&gt; SELECT * from tutorials_tbl \
   WHERE BINARY tutorial_author = 'sanjay';
Empty set (0.02 sec)

mysql&gt;

Fetching Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using where clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)
Sr.No.Parameter & Description
1$sqlRequired – SQL query to select records in a MySQL table using Where Clause.
2$resultmodeOptional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using where clause in a table −

&lt;html&gt;
   &lt;head&gt;
      &lt;title&gt;Using Where Clause&lt;/title&gt;
   &lt;/head&gt;
   &lt;body&gt;
      &lt;?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli-&gt;connect_errno ) {
            printf("Connect failed: %s&lt;br /&gt;", $mysqli-&gt;connect_error);
            exit();
         }
         printf('Connected successfully.&lt;br /&gt;');
   
         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
            FROM tutorials_tbl where tutorial_author = "Mahesh"';
		 
         $result = $mysqli-&gt;query($sql);
           
         if ($result-&gt;num_rows &gt; 0) {
            while($row = $result-&gt;fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d &lt;br /&gt;", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.&lt;br /&gt;');
         }
         mysqli_free_result($result);
         $mysqli-&gt;close();
      ?&gt;
   &lt;/body&gt;
&lt;/html&gt;

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

MySQL SELECT Statement | MySQL WHERE Clause
Show Buttons
Hide Buttons