MySQL Beginners Series — The SELECT statement

This free post is an excerpt from some of the premium MySQL blog posts and content I am writing over on my blog, Digital Owl’s Prose.

Learn more about this series and jump on the list for progress notification updates and release information…

Image by Manfred Steger from Pixabay

The SELECT statement in depth

We use the SELECT statement to see or read the data stored in a MySQL table. The simplest SELECT we can execute uses this syntax:

SELECT *
FROM table_name;

(✔️ Note: In MySQL, only the SELECT keyword — or clause as it is commonly referred to — is mandatory. The FROM clause is optional, although you will need to use the FROM clause to query data in a table).

All columns with SELECT

The asterisk (*) has special meaning in a SELECT statement. It means ‘all columns’. When you specify the asterisk in this way and a valid, existing table_name in the FROM clause, all columns from the named table are returned in the query results.

Let’s see an example using the ‘category’ table:

SELECT *
FROM category;

We can see that the ‘category‘ table has 16 rows of data with 3 columns:

• category_id
• name
• last_update

In this query, I used the SELECT keyword and the asterisk (*) on one line and then on a new line, the FROM keyword (often referred to as the FROM clause), along with the full table name ‘category‘. It is not necessary to place the FROM clause on an additional line. Again, this is a preference you will see in most queries for readability.

This query executes without error and returns the same results:

SELECT * FROM category;

Individual columns with SELECT

Specifying all table columns using SELECT * is a quick and easy way to see what columns are available in a table. However, you mostly want to avoid SELECT * queries in application production code and reporting, instead choosing just those columns needed for the purpose at hand.

Suppose we only need query results of just the name of the categories found in the ‘category‘ table. How can you get that information?

All we have to do is list out any column by name, in the SELECT statement list like this:

SELECT name
FROM category;

You can list out as many columns as you like but each must be separated by a comma. In this next example, I list the ‘name’ and ‘category_id’ columns:

SELECT name, category_id
FROM category;

Notice in the SELECT list, the column order does not matter and does not have to be in the same order as the columns are in the table named in the FROM clause.

SELECT column(s) and table name case-sensitivity

SELECT statement column names are not case-sensitive. This query executes without any errors:

SELECT NAME, CATEGORY_ID
FROM category;

I personally use — and recommend — lower-case column names as you will typically see this type of structure in the wild. On the other hand, table names are case-sensitive in certain environments. See this query and error for understanding:

SELECT name, category_id
FROM CATEGORY;
Error Code: 1146. Table 'sakila.CATEGORY' doesn't exist

To increase the likelihood that your queries are as portable as possible, use lower-case table names. Learn more by visiting the MySQL Identifier Case Sensitivity documentation.

Thank you for reading this excerpt. I would be most grateful for any constructive comments in order to make the content as best as possible for those who wish to start learning MySQL.

Feel free to contact me directly here with any suggestions or insights if you would like. Also, feel free to comment freely here.

I write about SQL, PHP, and CodeIgniter regularly over on my technical blog, Digital Owl’s Prose. Follow me there for similar content and be sure to sign up for my free weekly newsletter, OpenLampTech, the newsletter for PHP/MySQL Developers. Thank you!