MySQL SELECT statement

MySQL statement SELECT is used to extract records from one or more tables to MySQL.

Simple syntax for SELECT statement in MySQL

SELECT expressions
FROM tables
[WHERE conditions];

The full syntax for the SELECT statement in MySQL

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 ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]].
[LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value].
[PROCEDURE procedure_name]
[INTO [ OUTFILE 'file_name' options
| DUMPFILE 'file_name'
| @variable1, @variable2, ... @variable_n].
[FOR UPDATE | LOCK IN SHARE MODE];

Parameters and arguments of the statement

• ALL is optional. Returns all matching strings
• DISTINCT is optional. Removes duplicates from the result set. Learn more about DISTINCT.
• DISTINCTROW is optional. Synonym for DISTINCT. Removes duplicates from the results set.
• HIGH_PRIORITY – optional. It tells MySQL that it starts SELECT before any UPDATE operators waiting for the same resource. It can be used with MyISAM, MEMORY, and MERGE tables that use table-level locks.
• STRAIGHT_JOIN is optional. It tells MySQL to connect the tables in the order they are listed in the FROM sentence.
• SQL_SMALL_RESULT is optional. It uses fast temporary tables to store results (used with DISTINCT and GROUP BY).
• SQL_BIG_RESULT is optional. Prefers sorting rather than a temporary table to store results (used with DISTINCT and GROUP BY).
• SQL_BUFFER_RESULT is optional. Uses temporary tables to store results (cannot be used with subqueries).
• SQL_CACHE is optional. Saves results in a query cache.
• SQL_NO_CACHE is optional. It does not save results in the query cache.
• SQL_CALC_FOUND_ROWS is optional. Calculates how many records are in the result set (without taking into account the LIMIT attribute), which can then be obtained with FOUND_ROWS.
• expressions – The columns or calculations that you want to get. Use * if you want to select all columns.
• tables – the tables from which you want to get the records. There must be at least one table listed in the FROM sentence.
• WHERE conditions are optional. The conditions that must be met for the selected records.
• GROUP BY – optional. It collects data by several records and groups results by one or several columns. Learn more about GROUP BY.
• HAVING condition is optional. It is used in combination with GROUP BY to limit groups of returned rows to only those whose TRUE condition. Read more about HAVING.
• ORDER BY is optional. It is used to sort the entries in your result set. Learn more about ORDER BY.
• LIMIT is optional. If LIMIT is specified, it controls the maximum number of records to be retrieved. The maximum number of records specified by number_rows will be returned in the resulting set. The first line returned by LIMIT will be determined by the value offset_value.
• PROCEDURE is optional. If specified, it is the name of the procedure which should process the data in the resulting set.
• INTO is optional. If specified, this allows you to write the resulting set to a file or variable.

• FOR UPDATE – optional. Records affected by the query are blocked until the transaction is completed.
• LOCK IN SHARE MODE – optional. Records affected by the query may be used by other transactions, but cannot be updated or deleted by these and other transactions.

SELECTION OF ALL FIELDS FROM ONE TABLE

Let’s see how to use MySQL statement SELECT to select all fields from a table.

SELECT *
FROM order_details
WHERE quantity >= 100
ORDER BY DESC;

In this MySQL SELECT example, we used * to indicate that we want to select all fields from the order_details table where the number is greater than or equal to 100. The resulting set is sorted by quantity in descending order.

SELECTION OF INDIVIDUAL FIELDS FROM THE SAME TABLE

You can also use MySQL statement SELECT to select individual fields from a table.
For example:

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 300
ORDER BY quantity ASC, unit_price DESC;

In this MySQL example, SELECT returns only order_id, quantity, and unit_price fields from the order_details table where the number is less than 300. The results are sorted by quantity in ascending order and then unit_price in descending order.

SELECTION OF FIELDS FROM SEVERAL TABLES

You can also use MySQL statement SELECT to extract fields from multiple tables.

SELECT order_details.order_id, customers.customer_name
FROM customers
INNER JOIN order_details
ON customers.customer_id = order_details.customer_id
ORDER BY order_details.order_id;

In this MySQL example, SELECT connects two tables together to produce a result set that displays the order_id and customer_name fields where the customer_id value matches both the order_details and the order_details table. The results are sorted by the order_details.order_id the field in ascending order.

Write to file

You can also use MySQL statement SELECT to write a set of results to a file.
For example:

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 300
ORDER BY quantity
INTO OUTFILE 'result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"
LINES TERMINATED BY '\n';

In this MySQL example, SELECT returns only order_id, quantity, and unit_price fields from the order_details table where the quantity is less than 300. The results are sorted by quantity in ascending order and written to a file named result.txt.

Learning MySQL – SELECT Statements