w3resource

MySQL SELECT statement

SELECT statement

MySQL SELECT statement is used to retrieve rows from one or more tables. The statement can also include UNION statements and subqueries.

Syntax:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

Arguments:

Name Descriptions
SELECT SELECT statement is used to fetch rows or records from one or more tables. 
* , ALL Indicating all columns.
column Columns or list of columns.
table Indicates the name of the table from where the rows will be retrieved.
DISTINCT DISTINCT clause is used to retrieve unique rows from a table.
DISTINCTROW DISTINCTROW is a synonym for DISTINCT.
HIGH_PRIORITY If used with SELECT, the associated query obtains higher priority than an update statement. It runs even if the table on which the query is applied is locked by an update query. It can be used only with MyISAM, MEMORY, and MERGE storage engines. And can be used only if the associated query is very fast as well as done at once. If a SELECT statements are part of a UNION, you can not use HIGH_PRIORITY along with.
STRAIGHT_JOIN If used with SELECT, associated tables are joined in the order they are arranged in the corresponding FROM clause. This can not be used to make a query fast and also can be used in the table_references list.
SQL_SMALL_RESULT Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set is small, so use fast temporary tables to store the resulting table instead of using sorting.
SQL_BIG_RESULT Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set has many rows, so use disk-based temporary tables if needed, and prefer sorting to use a temporary table with a key on the GROUP BY elements.
SQL_BUFFER_RESULT It forces the result to be put into a temporary table. If used, MySQL can free the table locks early and takes less time to send the result set to the client where it may take a long time. It can not be used for subqueries or following UNION.
SQL_CACHE It tells MySQL to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ) if it is cacheable. The value of the query_cache_type system variable is 2 or DEMAND.
SQL_NO_CACHE It tells MySQL not to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ). It can also be used for views if it accompanies a SELECT statement.
SQL_CALC_FOUND_ROWS It tells MySQL to calculate the number of rows in a result set. While calculating rows in this fashion, LIMIT clause is ignored. The number of rows can then be retrieved with SELECT FOUND_ROWS().
select_expr An expression.
FROM This clause is used after SELECT and preceding tables or subqueries.
table_references Name of the tables used in a SELECT statement.
WHERE The conditions are supplied after this keyword (in a select statement).
where_condition Conditions placed after WHERE.
GROUP BY If used, the results returned from the field name used after GROUP BY clause is grouped together in result set.
col_name Name of the column or columns or fields.
expr An expression.
position Refers to the position of columns beginning with 1.
ASC If used, results are returned in ascending order.
DESC If used, results are returned in descending order.
WITH ROLLUP This modifier can be used with GROUP BY clause. If used, extra rows are added to the summary output.
HAVING This modifier can be used with GROUP BY clause and aggregate functions. Can not be used with WHERE clause.
ORDER BY MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement.
LIMIT It is always followed by one or two numeric arguments. The first argument is the offset of the first row to be returned and the second argument is the maximum number of rows returned in the result set.
PROCEDURE This clause names a procedure that should process the data in the result set.
INTO OUTFILE Takes a backup of the associated table in a file specified.
INTO DUMPFILE If used instead of INTO OUTFILE, only one row is written into the file. Useful if BLOB values are to be stored in a file.
var_name A variable name to store data temporarily.
FOR UPDATE If used with a storage engine that uses page or row locks, untill the end of the transaction, rows checked by the query are write-locked.
LOCK IN SHARE MODE If used, a shared lock is set upon the rows examined by the associated query. So, other transactions can read the examined rows, can not update or delete those rows.

Following clauses are commonly used in SELECT statements:

  • Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.
  • table_references indicates the table or tables from which to retrieve rows.
  • The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause (i.e without any condition).
  • In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate functions.

SELECT can also be used to retrieve rows computed without reference to any table.

For example:

mysql> SELECT 5 - 3 ;
+-------+
| 5 - 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

You can specify DUAL as a dummy table name where no tables are referenced :

mysql> SELECT 5 - 3 FROM DUAL;
+-------+
| 5 - 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. In the consequent pages of w3resource MySQL tutorial, you will find detail discussion accompanied by examples (often with PHP codes) of the said arguments of the SELECT statement.

MySQL selecting all data

MySQL SELECT statement without any condition retrieves all records from a table. The following SELECT statement will retrieve all data from publisher table.

SELECT *
FROM publisher;

Table: publisher


Output:

mysql> SELECT * FROM publisher;
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 | 
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 | 
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 | 
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 | 
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 | 
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 | 
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 | 
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 | 
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
8 rows in set (0.00 sec)

PHP script

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example-select-all-data - php MySQL examples | w3resource</title>
</head>
<body>
<?php
echo "<h2>List of the publishers with other detail : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='200' align='center'>Publisher's ID</td><td width='200'align='center'>Publisher's Name</td><td width='200' align='center'>Publisher's City</td><td width='200' align='center'>Publisher's Country</td><td width='200' align='center'>Country Office</td><td width='200' align='center'>Number of branches</td><td width='200' align='center'>Date of Establishment</td>";
echo "</tr>";
include("../dbopen.php");
$result = MySQL_query("SELECT * FROM publisher");
while($row=MySQL_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_id'] . "</td>";
echo "<td align='center' width='200'>" . $row['pub_name'] . "</td>";
echo "<td align='center' width='100'>" . $row['pub_city'] . "</td>";
echo "<td align='center' width='100'>" . $row['country'] . "</td>";
echo "<td align='center' width='100'>" . $row['country_office'] . "</td>";
echo "<td align='center' width='100'>" . $row['no_of_branch'] . "</td>";
echo "<td align='center' width='100'>" . $row['estd'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>

View the example in browser

MySQL SELECT specific rows

When a user wants to retrieve some individual rows from a table, a WHERE clause has to be added with the SELECT statement immediately followed by a condition.

Syntax:

SELECT * 
FROM  
WHERE 
where_condition

Here * indicates all columns.

Example:

This following SELECT statement will retrieve those particular rows where 'country' is the USA.

Code :

SELECT *
FROM publisher
WHERE country='USA'

Output:

>
mysql> SELECT * FROM publisher
    -> WHERE country='USA';
+--------+--------------------------+----------+---------+----------------+--------------+------------+
| pub_id | pub_name                 | pub_city | country | country_office | no_of_branch | estd       |
+--------+--------------------------+----------+---------+----------------+--------------+------------+
| P001   | Jex Max Publication      | New York | USA     | New York       |           15 | 1969-12-25 | 
| P005   | Mountain Publication     | Houstan  | USA     | Sun Diego      |           25 | 1975-01-01 | 
| P006   | Summer Night Publication | New York | USA     | Atlanta        |           10 | 1990-12-10 | 
+--------+--------------------------+----------+---------+----------------+--------------+------------+
3 rows in set (0.01 sec)

MySQL SELECT specific rows with AND operator

MySQL AND operator is used to combine more than one conditions aiming to fetch records when both of the conditions are satisfied. The following SELECT statement will retrieve those particular rows where country and city of the publisher are 'USA' and 'New York'.

Code:

SELECT *
FROM publisher
WHERE country='USA'
AND pub_city='New York';

Output:

mysql> SELECT * FROM publisher
    -> WHERE country='USA'
    -> AND pub_city='New York';
+--------+--------------------------+----------+---------+----------------+--------------+------------+
| pub_id | pub_name                 | pub_city | country | country_office | no_of_branch | estd       |
+--------+--------------------------+----------+---------+----------------+--------------+------------+
| P001   | Jex Max Publication      | New York | USA     | New York       |           15 | 1969-12-25 | 
| P006   | Summer Night Publication | New York | USA     | Atlanta        |           10 | 1990-12-10 | 
+--------+--------------------------+----------+---------+----------------+--------------+------------+
2 rows in set (0.00 sec)

MySQL SELECT specific columns

To retrieve records from specific columns, you need to specify a list of comma separated columns. The following MySQL statement returns the name of the book, author id, and price of the books from book_mast table.

Table: book_mast


Code:

SELECT book_name,aut_id,book_price
FROM book_mast;

Output:

mysql> SELECT book_name,aut_id,book_price
    -> FROM book_mast;
+-------------------------------------+--------+------------+
| book_name                           | aut_id | book_price |
+-------------------------------------+--------+------------+
| Introduction to Electrodynamics     | AUT001 |      85.00 | 
| Understanding of Steel Construction | AUT002 |     105.50 | 
| Guide to Networking                 | AUT003 |     200.00 | 
| Transfer  of Heat and Mass          | AUT004 |     250.00 | 
| Conceptual Physics                  | AUT005 |     145.00 | 
| Fundamentals of Heat                | AUT006 |     112.00 | 
| Advanced 3d Graphics                | AUT007 |      56.00 | 
| Human Anatomy                       | AUT008 |      50.50 | 
| Mental Health Nursing               | AUT009 |     145.00 | 
| Fundamentals of Thermodynamics      | AUT010 |     225.00 | 
| The Experimental Analysis of Cat    | AUT011 |      95.00 | 
| The Nature  of World                | AUT005 |      88.00 | 
| Environment a Sustainable Future    | AUT012 |     100.00 | 
| Concepts in Health                  | AUT013 |     180.00 | 
| Anatomy & Physiology                | AUT014 |     135.00 | 
| Networks and Telecommunications     | AUT015 |      45.00 | 
+-------------------------------------+--------+------------+
16 rows in set (0.01 sec)

MySQL select specific columns with distinct operator

DISTINCT clause is used to retrieve unique rows from a table. The following MySQL statement retrieves the unique author ids from book_mast table.

Code:

SELECT DISTINCT aut_id
FROM book_mast;

Output:

mysql> SELECT DISTINCT aut_id
    -> FROM book_mast;
+--------+
| aut_id |
+--------+
| AUT001 | 
| AUT002 | 
| AUT003 | 
| AUT004 | 
| AUT005 | 
| AUT006 | 
| AUT007 | 
| AUT008 | 
| AUT009 | 
| AUT010 | 
| AUT011 | 
| AUT012 | 
| AUT013 | 
| AUT014 | 
| AUT015 | 
+--------+
15 rows in set (0.01 sec)

Note : If you look at the book_mast table shown under Sample table : book_mast heading, you would find that AUT005 (in 'aut_id' column) has appeared more than once. Using DISTINCT clause, we got rid of this redundancy.

MySQL SELECT specific columns with logical OR operator

OR operator retrieves records from a table if at least one of the given conditions is satisfied. The following MySQL statement retrieves records of pub_name, country, pub_city columns from publisher table if either Country (i.e. country) of the publisher is 'USA' or his city (i.e. pub_city) is 'New York'.

Code:

SELECT pub_name, country,pub_city
FROM publisher
WHERE country='USA' OR pub_city='New York';

Output:

mysql> SELECT pub_name,country,pub_city
    -> FROM publisher
    -> WHERE country='USA' OR pub_city='New York';
+--------------------------+---------+----------+
| pub_name                 | country | pub_city |
+--------------------------+---------+----------+
| Jex Max Publication      | USA     | New York | 
| Mountain Publication     | USA     | Houstan  | 
| Summer Night Publication | USA     | New York | 
+--------------------------+---------+----------+
3 rows in set (0.00 sec)

MySQL sorting rows in ascending order

MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement. By default, columns are sorted in ascending order. You can use ASC keyword to achieve the same result.

Note : In the case of character type column sorting the sorting is dependent upon case sensitivity. The default sort order is ascending this means smallest value comes first. To sort in reverse order, DESC key has to be used.

In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against pub_name column. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.

Code:

SELECT pub_name, country,pub_city 
FROM publisher
ORDER BY pub_name;

Output:

mysql> SELECT pub_name,country,pub_city
    -> FROM publisher
    -> ORDER BY pub_name;
+------------------------------+-----------+-----------+
| pub_name                     | country   | pub_city  |
+------------------------------+-----------+-----------+
| BPP Publication              | India     | Mumbai    | 
| Jex Max Publication          | USA       | New York  | 
| Mountain Publication         | USA       | Houstan   | 
| New Harrold Publication      | Australia | Adelaide  | 
| Novel Publisher Ltd.         | India     | New Delhi | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 
| Summer Night Publication     | USA       | New York  | 
| Ultra Press Inc.             | UK        | London    | 
+------------------------------+-----------+-----------+
8 rows in set (0.02 sec)

MySQL sorting rows in descending order

The following MySQL statement sort rows of a table in descending order using ORDER BY clause.

Code:

SELECT pub_name, country,pub_city
FROM publisher
ORDER BY pub_name DESC;

Output:

mysql> SELECT pub_name,country,pub_city
    -> FROM publisher
    -> ORDER BY pub_name DESC;
+------------------------------+-----------+-----------+
| pub_name                     | country   | pub_city  |
+------------------------------+-----------+-----------+
| Ultra Press Inc.             | UK        | London    | 
| Summer Night Publication     | USA       | New York  | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 
| Novel Publisher Ltd.         | India     | New Delhi | 
| New Harrold Publication      | Australia | Adelaide  | 
| Mountain Publication         | USA       | Houstan   | 
| Jex Max Publication          | USA       | New York  | 
| BPP Publication              | India     | Mumbai    | 
+------------------------------+-----------+-----------+
8 rows in set (0.00 sec)

MySQL sorting rows on multiple columns

Sort can be performed on multiple columns. The way this type of sort happen is, firstly the rows will be sorted on the first column then the rows will be sorted on the second column whose first column's data are same.

In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against country and pub_city columns. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.

Code:

SELECT pub_name, country,pub_city
FROM publisher
ORDER BY country,pub_city;

Output :

mysql> SELECT pub_name, country, pub_city
    -> FROM publisher
    -> ORDER BY country, pub_city;
+------------------------------+-----------+-----------+
| pub_name                     | country   | pub_city  |
+------------------------------+-----------+-----------+
| New Harrold Publication      | Australia | Adelaide  | 
| BPP Publication              | India     | Mumbai    | 
| Novel Publisher Ltd.         | India     | New Delhi | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 
| Ultra Press Inc.             | UK        | London    | 
| Mountain Publication         | USA       | Houstan   | 
| Jex Max Publication          | USA       | New York  | 
| Summer Night Publication     | USA       | New York  | 
+------------------------------+-----------+-----------+
8 rows in set (0.00 sec)

MySQL select with NULL value

IS NULL, IS NOT NULL is used to select or test if a value stored in a table is NULL. While writing a MySQL statement, NULL keyword is used to specify a null value.

What is NULL value ?

  • A value of NULL says that the value is unknown, not applicable or will be added later.
  • A value of NULL is not an empty or zero value.
  • No two null values are equal.
  • Since a value of NULL is unknown, comparisons between two null values, or between a NULL and any other value, returns unknown.

Example of SELECT with NULL :

Code:

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

Output:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1> NULL;
+----------+-----------+----------+---------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1> NULL |
+----------+-----------+----------+---------+
|     NULL |      NULL |     NULL |    NULL | 
+----------+-----------+----------+---------+
1 row in set (0.01 sec)

Example of SELECT with IS NULL, IS NOT NULL :

Code:

SELECT 1 IS NULL, 1 IS NOT NULL;

Output:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 | 
+-----------+---------------+
1 row in set (0.00 sec)

Previous: MySQL Delete
Next: MySQL UNION



Follow us on Facebook and Twitter for latest update.