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:

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 :

-- This SQL statement selects the result of subtracting 3 from 5.
SELECT 5 - 3 FROM DUAL;

Explanation:

• The purpose of this SQL code is to perform a subtraction operation and select the result.


• SELECT 5 - 3: This line calculates the result of subtracting 3 from 5.


• FROM DUAL: The DUAL table in MySQL is a special one-row, one-column table present by default. It is typically used in SQL queries to perform calculations or evaluate expressions that don't require data from any specific table. In this case, FROM DUAL is used to signify that the subtraction operation is not dependent on any particular table's data.

Output:

+-------+
| 5 - 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

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.

-- This SQL statement selects all columns from the publisher table.
SELECT *
-- Specifies the table from which to select data, in this case, the publisher table.
FROM publisher;

Explanation:

• The purpose of this SQL code is to retrieve all rows and columns from the publisher table.


• SELECT *: This line specifies that all columns should be included in the result set.


• FROM publisher: This line specifies the table from which to retrieve data, which is the publisher table.

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 <table_name>
WHERE <expression>
where_condition

Here * indicates all columns.

Example:

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

Code :

-- This SQL statement selects all columns from the publisher table where the country is 'USA'.
SELECT *
-- Specifies the table from which to select data, in this case, the publisher table.
FROM publisher
-- Specifies a condition to filter the rows, only selecting rows where the country column is 'USA'.
WHERE country = 'USA';

Explanation:

• The purpose of this SQL code is to retrieve all rows and columns from the publisher table where the country column has the value 'USA'.


• SELECT *: This line specifies that all columns should be included in the result set.


• FROM publisher: This line specifies the table from which to retrieve data, which is the publisher table.


• WHERE country = 'USA': This line filters the rows, ensuring that only rows with the value 'USA' in the country column are included in the result set.

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:

-- This SQL statement selects all columns from the publisher table where the country is 'USA' and the pub_city is 'New York'.
SELECT *
-- Specifies the table from which to select data, in this case, the publisher table.
FROM publisher
-- Specifies the condition to filter the rows, only selecting rows where the country column is 'USA'.
WHERE country = 'USA'
-- Specifies an additional condition to further filter the rows, only selecting rows where the pub_city column is 'New York'.
AND pub_city = 'New York';

Explanation:

• The purpose of this SQL code is to retrieve all rows and columns from the publisher table where the country column has the value 'USA' and the pub_city column has the value 'New York'.


• SELECT *: This line specifies that all columns should be included in the result set.


• FROM publisher: This line specifies the table from which to retrieve data, which is the publisher table.


• WHERE country = 'USA' AND pub_city = 'New York': This line specifies the conditions to filter the rows. It ensures that only rows with the value 'USA' in the country column and 'New York' in the pub_city column are included in the result set.

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:

-- This SQL statement selects specific columns book_name, aut_id, and book_price from the book_mast table.
SELECT book_name, aut_id, book_price
-- Specifies the columns to be selected in the query, which are book_name, aut_id, and book_price.
FROM book_mast;
-- Specifies the table from which to select data, in this case, the book_mast table.

Explanation:

• The purpose of this SQL code is to retrieve specific columns (book_name, aut_id, and book_price) from the book_mast table.


• SELECT book_name, aut_id, book_price: This line specifies the columns to be included in the result set: book_name, aut_id, and book_price.


• FROM book_mast: This line specifies the table from which to retrieve the data, which is the book_mast table.

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:

-- This SQL statement selects distinct values of aut_id from the book_mast table.
SELECT DISTINCT aut_id
-- Specifies the column aut_id to be selected, and the DISTINCT keyword ensures that only unique values are returned.
FROM book_mast;
-- Specifies the table from which to select data, in this case, the book_mast table.

Explanation:

• The purpose of this SQL code is to retrieve unique values of the aut_id column from the book_mast table.


• SELECT DISTINCT aut_id: This line specifies the column aut_id to be included in the result set, and the DISTINCT keyword ensures that only unique values of aut_id are returned.


• FROM book_mast: This line specifies the table from which to retrieve the data, which is the book_mast table.

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:

-- This SQL statement selects columns pub_name, country, and pub_city from the publisher table
-- where the country is 'USA' or the pub_city is 'New York'.
SELECT pub_name, country, pub_city
-- Specifies the columns to be selected in the query: pub_name, country, and pub_city.
FROM publisher
-- Specifies the table from which to select data, which is the publisher table.
WHERE country='USA' OR pub_city='New York';
-- Specifies the condition for filtering rows, selecting only those where the country is 'USA' or the pub_city is 'New York'.

Explanation:

• The purpose of this SQL code is to retrieve specific columns (pub_name, country, and pub_city) from the publisher table where either the country is 'USA' or the pub_city is 'New York'.


• SELECT pub_name, country, pub_city: This line specifies the columns to be included in the result set: pub_name, country, and pub_city.


• FROM publisher: This line specifies the table from which to retrieve the data, which is the publisher table.


• WHERE country='USA' OR pub_city='New York': This line specifies the condition for filtering rows. It ensures that only rows where the country is 'USA' or the pub_city is 'New York' are included in the result set.

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:

-- This SQL statement selects columns pub_name, country, and pub_city from the publisher table
-- and orders the result set by the pub_name column in ascending order.
SELECT pub_name, country, pub_city
-- Specifies the columns to be selected in the query: pub_name, country, and pub_city.
FROM publisher
-- Specifies the table from which to select data, which is the publisher table.
ORDER BY pub_name;
-- Specifies the ordering of the result set based on the pub_name column, in ascending order.

Explanation:

• The purpose of this SQL code is to retrieve specific columns (pub_name, country, and pub_city) from the publisher table and order the result set based on the pub_name column in ascending order.


• SELECT pub_name, country, pub_city: This line specifies the columns to be included in the result set: pub_name, country, and pub_city.


• FROM publisher: This line specifies the table from which to retrieve the data, which is the publisher table.


• ORDER BY pub_name: This line specifies the ordering of the result set. It orders the rows based on the pub_name column, arranging them in ascending alphabetical order.

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:

-- This SQL statement selects columns pub_name, country, and pub_city from the publisher table
-- and orders the result set by the pub_name column in descending order.
SELECT pub_name, country, pub_city
-- Specifies the columns to be selected in the query: pub_name, country, and pub_city.
FROM publisher
-- Specifies the table from which to select data, which is the publisher table.
ORDER BY pub_name DESC;
-- Specifies the ordering of the result set based on the pub_name column, in descending order.

Explanation:

• The purpose of this SQL code is to retrieve specific columns (pub_name, country, and pub_city) from the publisher table and order the result set based on the pub_name column in descending order.


• SELECT pub_name, country, pub_city: This line specifies the columns to be included in the result set: pub_name, country, and pub_city.


• FROM publisher: This line specifies the table from which to retrieve the data, which is the publisher table.


• ORDER BY pub_name DESC: This line specifies the ordering of the result set. It orders the rows based on the pub_name column, arranging them in descending alphabetical order.

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:

-- This SQL statement selects columns pub_name, country, and pub_city from the publisher table
-- and orders the result set first by the country column in ascending order, and then by the pub_city column in ascending order.
SELECT pub_name, country, pub_city
-- Specifies the columns to be selected in the query: pub_name, country, and pub_city.
FROM publisher
-- Specifies the table from which to select data, which is the publisher table.
ORDER BY country, pub_city;
-- Specifies the ordering of the result set. It orders the rows first by the country column, then by the pub_city column, both in ascending order.

Explanation:

• The purpose of this SQL code is to retrieve specific columns (pub_name, country, and pub_city) from the publisher table and order the result set first by the country column in ascending order and then by the pub_city column in ascending order.


• SELECT pub_name, country, pub_city: This line specifies the columns to be included in the result set: pub_name, country, and pub_city.


• FROM publisher: This line specifies the table from which to retrieve the data, which is the publisher table.


• ORDER BY country, pub_city: This line specifies the ordering of the result set. It orders the rows first by the country column and then by the pub_city column, both in ascending order.

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:

-- This SQL statement compares the value 1 with NULL using different operators.
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
-- The SELECT statement returns the results of each comparison.

Explanation:

• The purpose of this SQL code is to demonstrate how comparisons between the value 1 and NULL behave using different comparison operators.


• SELECT 1 = NULL: This line compares the value 1 with NULL using the equals operator (=). However, comparing a value with NULL using the equals operator always results in NULL, not true or false.


• SELECT 1 <> NULL: This line compares the value 1 with NULL using the not equals operator (<>). Similar to the equals operator, comparing a value with NULL using the not equals operator also results in NULL.


• SELECT 1 < NULL: This line compares the value 1 with NULL using the less than operator (<). Any comparison involving NULL results in NULL, so the result of this comparison is NULL.


• SELECT 1 > NULL: This line compares the value 1 with NULL using the greater than operator (>). Similar to the previous comparisons, the result of this comparison is also 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:

-- This SQL statement checks whether the value 1 is NULL or not NULL using the IS NULL and IS NOT NULL operators.
SELECT 1 IS NULL, 1 IS NOT NULL;
-- The SELECT statement returns the results of each IS NULL and IS NOT NULL check.

Explanation:

• The purpose of this SQL code is to demonstrate how to use the IS NULL and IS NOT NULL operators to check whether a value is NULL or not NULL.


• SELECT 1 IS NULL: This line checks whether the value 1 is NULL using the IS NULL operator. However, since the value 1 is not NULL, the result of this expression is FALSE.


• SELECT 1 IS NOT NULL: This line checks whether the value 1 is not NULL using the IS NOT NULL operator. Since the value 1 is indeed not NULL, the result of this expression is TRUE

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