# SELECT Statements

# Basic SELECT Statements

The SELECT statement is used to retrieve information from one or more database tables. It starts with the SELECT keyword followed by a list of columns to return from the specified table or tables. Columns are separate by a comma. The asterisk (*) can be used to return all columns from the specified table or tables.

The following command will retrieve all columns and rows from the products table.

SELECT * FROM products

# Filtered SELECT Statements

It is often the case that we will want to retrieve a specific row or rows from a table. For example, we may want all of the products with the price less than $30.

This filtered result can be accomplished using the WHERE clause. The WHERE clause is followed by a condition that is tested against each row. If the row meets that condition, it will be returned.

SELECT * 
FROM products
WHERE product_price < 30.00

# Sorted SELECT Statements

It is possible to sort the results using the ORDER BY clause. The ORDER BY clause is followed by the list of columns to sort. Columns can be sorted in ascending (ASC) or descending order (DESC) with ascending being the default.

SELECT * 
FROM products
WHERE product_price < 30.00 
ORDER BY product_name DESC

# Limited SELECT Statements

Another way to limit the results returned from the database is use the LIMIT clause. For example, if we only want 5 rows to be returned. The LIMIT clause is followed by a number, which is the number of rows to be return OR in the case of the UPDATE or DELETE command the number of rows affected.

SELECT * 
FROM products
WHERE product_price < 30.00 
ORDER BY product_name DESC
LIMIT 5

Pagination can be accomplished by inserting a second number after the LIMIT clause. This offset will be used to tell the database to start the row count a specified number of rows. For MySQL the syntax is LIMIT offset, row count. So, if we wanted to retrieve the third page of products it can be accomplished with the following:

SELECT * 
FROM products
WHERE product_price < 30.00 
ORDER BY product_name DESC
LIMIT 10, 5