MySQL TABLE statement – Example queries

Recently, I learned through some online reading that the TABLE statement is supported in MySQL. I was aware that PostgreSQL supported the TABLE command and used it from time to time. As time went on and I used MySQL more (nothing at all against PostgreSQL) I forgot about the TABLE statement. In this blog post, I’ll cover some rudimentary uses of the MySQL TABLE statement for any other developers who may not be aware that it too exists in MySQL…

OS and DB used:

• Linux Mint 20 “Ulyana”
• MySQL 8.0.23

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

MySQL TABLE Statement: Syntax and basics

The MySQL TABLE statement returns query results from a table in a similar fashion as if you had executed a SELECT * FROM type of query.

For the above query, the MySQL TABLE statement equivalent would look like this:

In the first set of queries, both statements return the exact same results: all columns and rows from the named table. Notice the syntax for the MySQL TABLE statement is simply the TABLE keyword followed by the table name. There is no FROM clause.

MySQL TABLE Statement: Specifying certain columns

It is perfectly valid to specify 1 or more columns in the SELECT clause, to be returned in the query results:

However, the same is not true for the MySQL TABLE statement as shown in the next example:

Omitting the FROM clause still results in an error as above:

MySQL TABLE Statement: Ordering result rows

To impose any determinate sorting on query results, you must use an ORDER BY clause. In the below query, I order the results by the ‘first_name’ column, in ascending order. A pretty common and standard query:

I’ve also constrained – not filtered – the number of rows displayed by using the LIMIT clause. Mostly for a more manageable output in the post. But, can you execute a similar type of query using the MySQL TABLE statement?

You can, in fact, use ORDER BY and LIMIT clauses in a TABLE query just the same as you would with SELECT.

Extra: The OFFSET clause is allowed in a TABLE query as well.

MySQL TABLE Statement: Filtering result rows

Filtering of rows in SQL happens in the WHERE clause. For example, if you only wanted to see row(s) for ‘actor_id’ 71, place that predicate test in the query’s WHERE clause:

On the contrary, including the WHERE clause in a MySQL TABLE statement query is not allowed:

Consider making a small donation on my behalf as I continue sharing useful and valuable content here on my blog.

MySQL TABLE Statement: Can you use it on a VIEW?

You can retrieve rows and columns from a VIEW just like a regular table using the MySQL TABLE statement as shown below:

Visit the official TABLE documentation for more information on this command.

If you are familiar with TABLE and use it often, let me know in the comments below.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.