Blog

In this guide, we will explain how to use the MySQL SELECT statement with syntax and examples.

Description

The MySQL SELECT statement is used to retrieve records from one or more tables in MySQL.

Syntax

In its simplest form, the syntax for the SELECT statement in MySQL is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the SELECT statement in MySQL is:

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 expressions]
[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 or Arguments

ALLOptional. Returns all matching rowsDISTINCTOptional. Removes duplicates from the result set. Learn more about DISTINCT clause.DISTINCTROWOptional. Synonym for DISTINCT. Removes duplicates from the result set.HIGH_PRIORITYOptional. It tells MySQL to run the SELECT before any UPDATE statements that are waiting for the same resource. It may be used with MyISAM, MEMORY and MERGE tables that use table-level locking.STRAIGHT_JOINOptional. It tells MySQL to join the tables in the order that they are listed in the FROM clause.SQL_SMALL_RESULTOptional. Uses fast temporary tables to store results (used with DISTINCT and GROUP BY).SQL_BIG_RESULTOptional. Prefers sorting rather than using a temporary table to store results (used with DISTINCT and GROUP BY).SQL_BUFFER_RESULTOptional. Uses temporary tables to store results (can not be used with subqueries).SQL_CACHEOptional. Stores the results in the query cache.SQL_NO_CACHEOptional. Does not store the results in the query cache.SQL_CALC_FOUND_ROWSOptional. Calculates how many records are in the result set (not taking into account the LIMIT modifier) which can then be retrieved using the FOUND_ROWS function.expressionsThe columns or calculations that you wish to retrieve. Use * if you wish to select all columns.tablesThe tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.WHERE conditionsOptional. The conditions that must be met for the records to be selected.GROUP BY expressionsOptional. It collects data across multiple records and groups the results by one or more columns. Learn more about the GROUP BY clause. HAVING conditionOptional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE. Learn more about the HAVING clause. ORDER BY expressionOptional. It is used to sort the records in your result set. Learn more about the ORDER BY clause. LIMITOptional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.PROCEDUREOptional. If provided, procedure_name is the name of the procedure that should process the data in the result set. INTO

Optional. If provided, it allows you to write the result set to either a file or variable.

FOR UPDATEOptional. Records affected by the query are write-locked until the transaction has completed.LOCK IN SHARE MODEOptional. Records affected by the query can be used by other transactions but can not be updated or deleted by those other transactions.

Example – Select all fields from one table

Let’s look at how to use a MySQL SELECT query to select all fields from a table.

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

In this MySQL SELECT statement example, we’ve used * to signify that we wish to select all fields from the order_details table where the quantity is greater than or equal to 10. The result set is sorted by quantity in descending order.

Example – Select individual fields from one table

You can also use the MySQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

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. The results are sorted by quantity in ascending order and then unit_price in descending order.

Example – Select fields from multiple tables

You can also use the MySQL SELECT statement to retrieve 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_id;

This MySQL SELECT example joins two tables together to gives us a result set that displays the order_id and customer_name fields where the customer_id value matches in both the customers and order_details table. The results are sorted by order_id in ascending order.

Example – write to a file

You can also use the MySQL SELECT statement to write the result set to a file.

For example:

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

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. The results would be sorted by quantity in ascending order and written to a file called results.txt.

Next Topic : Click Here