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…
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:
(✔️ 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:
We can see that the ‘category‘ table has 16 rows of data with 3 columns:
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:
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
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
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
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
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.