MySQL Select
Summary: in this tutorial, you’ll learn how to use the MySQL SELECT
statement without referencing any table.
Typically, you use a SELECT
statement to select data from a table in the database:
SELECT select_list
FROM table_name;
Code language: SQL (Structured Query Language) (sql)
MySQL doesn’t require the FROM
clause. It means that you can have a SELECT
statement without the FROM
clause like this:
SELECT select_list;
Code language: SQL (Structured Query Language) (sql)
Here’s a trivial example:
SELECT 1 + 1;
Code language: SQL (Structured Query Language) (sql)
Output:
+-------+
| 1 + 1 |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
MySQL has many built-in functions like string, date, and Math functions. And you can use the SELECT
statement to execute these functions.
The following example returns the current date and time of the MySQL server:
SELECT NOW();
Code language: SQL (Structured Query Language) (sql)
Output:
+---------------------+
| NOW() |
+---------------------+
| 2021-07-26 08:08:02 |
+---------------------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
The NOW()
function returns the current date & time of the server on which MySQL runs. The NOW()
function doesn’t have any parameters. To call it, you place the parentheses () after the function name.
If a function has parameters, you need to pass arguments into it. For example, concatenate strings into one string, you can use the CONCAT()
function:
SELECT CONCAT('John',' ','Doe');
Code language: SQL (Structured Query Language) (sql)
Output:
+--------------------------+
| CONCAT('John',' ','Doe') |
+--------------------------+
| John Doe |
+--------------------------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
The CONCAT() function accepts one or more strings and concatenates them into a single string.
The dual table
Sometimes, you still need to use the FROM
clause but you don’t want to reference any actual table. In this case, you can use the dual table in the FROM
clause:
SELECT select_list
FROM dual;
Code language: SQL (Structured Query Language) (sql)
The dual table is a dummy table, not an actual table.
The dual is also necessary in case you need other clauses of the SELECT
statement. Without the FROM
clause, the SELECT
statement would not be valid.
A quick introduction to the column alias
By default, MySQL uses the expression specified in the SELECT
clause as the column name of the result set. To change a column name of the result set, you can use a column alias:
SELECT expression AS column_alias;
Code language: SQL (Structured Query Language) (sql)
To assign an alias to a column, you place the AS keyword after the expression followed by a column alias. The AS
keyword is optional, so you can skip it like this:
SELECT expression column_alias;
Code language: SQL (Structured Query Language) (sql)
For example:
SELECT CONCAT('John',' ','Doe') AS name;
Code language: SQL (Structured Query Language) (sql)
Output:
+----------+
| name |
+----------+
| John Doe |
+----------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
If the column alias contains spaces, you need to place it inside quotes like this:
SELECT CONCAT('Jane',' ','Doe') AS 'Full name';
Code language: SQL (Structured Query Language) (sql)
Output:
+-----------+
| Full name |
+-----------+
| John Doe |
+-----------+
1 row in set (0.00 sec)
Code language: plaintext (plaintext)
Summary
- MySQL
SELECT
statement doesn’t require theFROM
clause - Use the
dual
table if you want to use theFROM
clause but don’t want to reference a table. - Assign an alias to a column to make it more readable.