MySQL SELECT

 MySQL SELECT

Summary: in this tutorial, you will learn how to use the basic form of the MySQL SELECT statement to query data from a table.

Introduction to MySQL SELECT statement

The SELECT the statement allows you to read data from one or more tables. To write a SELECT statement in MySQL, you follow this syntax:

SELECT select_list
FROM table_name;

Let’s look at each part of the statement.

First, you start with the SELECT keyword. The keyword has a special meaning in MySQL. In this case, SELECT instructs MySQL to retrieve data.

Next, you have space and then a list of columns or expressions that you want to show in the result.

Then, you have the FROM keyword, space, and the name of the table.

Finally, you have a semicolon at the end of the statement.

The semicolon ; is the statement delimiter. It specifies the end of a statement. If you have two or more statements, you use the semicolon to separate them so that MySQL will execute each statement individually.

In the SELECT statement, the SELECT and FROM are keywords and written in capital letters. Basically, it is just about formatting. The uppercase letters make the keywords stand out.

Since SQL is not a case-sensitive language, you can write the keywords in lowercase e.g., select and from, the code will still run.

It is also important to note that the FROM the keyword is on a new line. MySQL doesn’t require this. However, placing the FROM keyword on a new line will make the query easier to read and simpler to maintain.

When evaluating the SELECT the statement, MySQL evaluates the FROM clause first and then the SELECT clause:

MySQL SELECT statement examples

We will use the table employees in the sample database to demonstrate how to use the SELECT statement:

The table employees has eight columns: employee number, last name, first name, extension, email, office code, report to, and job title. It also has many rows as shown in the following picture:

A) Using the MySQL SELECT statement to retrieve data from a single-column example

The following example uses the SELECT statement to select the last names of all employees:

SELECT lastName
FROM employees;

Here is the partial output:

The output of a SELECT the statement is called results or a result set as it’s a set of data that results from a query.

B) Using the MySQL SELECT statement to query data from multiple columns example

The following example uses the SELECT statement to get the first name, last name, and job title of employees:

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees;

Even though the employees the table has many columns, the SELECT the statement just returns data of three columns of all rows in the table as highlighted in the following picture:

The following picture shows the result set:

C) Using the MySQL SELECT statement to retrieve data from all columns example

If you want to retrieve data from all the columns of the employees table, you can specify all the column names in the SELECT clause.

Or you just use the asterisk (*) shorthand as shown in the following query:

SELECT * 
FROM employees;

The query returns data from all columns of the  employees table.

Notes about SELECT star

The SELECT * is often called “select star” or “select all” since you select all data from a table.

It is a good practice to use them SELECT * for ad-hoc queries only. If you embed the SELECT statement in the code such as PHP, Java, Python, or Node.js, you should explicitly specify the name of columns from which you want to get data for the following reasons:

• The SELECT * returns data from the columns that you may not use. It produces unnecessary I/O disk and network traffic between the MySQL database server and the application.
• When you explicitly specify the column names, the result set is predictable and easier to manage. However, if you use the SELECT * If someone changes the table by adding more columns, you will end up with a result set that is different from the one that you expected.
• Using the SELECT * may expose sensitive information to unauthorized users.

In this tutorial, you’ve learned how to use the basic MySQL SELECT statement to query data from a single table.