The SELECT statement is probably the most commonly used statement in MySQL (and SQL in general). It’s commonly used to return data from a table or view.

But SELECT isn’t the only way we can return data in MySQL. Below are two alternative methods we can use to return data in MySQL.

The TABLE Statement

We can use the MySQL TABLE statement to return all the contents of a table in the database.

Example:

TABLE Person;

Example result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+

In this case my database already had a table called Person with the above data. The TABLE statement allowed me to select all data from that table. The TABLE statement provides us with a more concise way of doing it over using the SELECT statement.

The above statement provides us with the same result that we’d get if we were to do this:

SELECT * FROM Person;

Example result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+

The TABLE statement is somewhat limited though. It doesn’t allow us to filter the results with a WHERE clause like we can with the SELECT statement. That said, it does provide a quick way to get all rows and columns from a table. It does support the ORDER BY clause and LIMIT clause though.

Another thing to be aware of is that the TABLE statement doesn’t return invisible columns. To get any invisible columns we’d need to use the SELECT statement, and explicitly name the invisible column/s in the SELECT list.

The VALUES Statement

In MySQL, VALUES is a table value constructor which also functions as a standalone SQL statement. It is probably most commonly known for being used to insert values into a table. However, perhaps less commonly known is its ability to run as a stand alone statement.

When we run VALUES as a stand alone statement we can get a result set of tabular that we specify in the actual VALUES statement. So to be clear, we’re not using it to return data from a table or view. We’re using it to return the data that we specify within the actual statement.

Here’s an example of using VALUES as a stand alone statement:

VALUES 
    ROW( 1, 'Homer', 'Simpson'), 
    ROW( 2, 'Lisa', 'Simpson'), 
    ROW( 3, 'Bart', 'Simpson');

Example result:

+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 | Homer    | Simpson  |
|        2 | Lisa     | Simpson  |
|        3 | Bart     | Simpson  |
+----------+----------+----------+

By default, the columns are named column_0, column_1, column_2, and so on.

We can change the column names by providing column aliases, but this will require the use of the SELECT statement. To do this, we can run the VALUES statement as a subquery for an outer SELECT statement:

SELECT * FROM (
    VALUES 
        ROW( 1, 'Homer', 'Simpson'), 
        ROW( 2, 'Lisa', 'Simpson'), 
        ROW( 3, 'Bart', 'Simpson')   
    ) AS Person( PersonId, FirstName, LastName );

Example result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+