MySQL SELECT

Summary: in this chapter, we will learn how to use MySQL SELECT statement to query data from tables.

Using MySQL SELECT Statement to Query Data

Introducing to MySQL SELECT statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

Let’s take look into the syntax of the SELECT statement:

1 2 3 4 5 6 7 8 9 10 11

SELECT   column_1, column_2, ... FROM   table_1 [INNER] | LEFT |RIGHT] JOIN table_2 ON conditions WHERE   conditions GROUP BY column_1 HAVING group_conditions ORDER BY column_1 LIMIT OFFSET, LENGTH;

The SELECT and FROM clause are required in the statement. Other parts are optional and later  we will learn other clause in more detail in this tutorial.

Here is short description of several clauses as explained in the following list:

• SELECT followed by a list of comma-separated columns or an asterisk (*) to indicate that you want to return all columns.
• FROM specifies the table where you want to query the data.
• JOIN gets data from other tables based on certain join conditions.
• WHERE filters rows in the result set.
• GROUP BY groups a set of rows into groups.
• HAVING filters group based on groups defined by GROUP BY clause.
• ORDER BY specifies a list of columns for sorting.
• LIMIT constrains the number of returned rows.

See the following employees table in the sample database.  It has 10 columns which are employeeid, firstname, lastname, dob, email, jobtitle, salary, reportto, branchid, departmentid and many rows data.

The SELECT statement controls which columns and rows that you want to see so it is only for view the database.

MySQL SELECT statement examples

CASE 1:

The following MySQL SELECT statement selects the “firstname” , “lastname” and “jobtitle” columns from the “employees” table:

1 2 3 4

SELECT  lastname, firstname, jobtitle FROM  employees;

Even though there are many columns in employees table but the SELECT statement just returns data of three columns of all rows. We can see in the below picture:

CASE 2:

The following MySQL SELECT statement selects all the columns from the “employees” table:

1	SELECT * FROM employees;

You should use the asterisk (*) for testing only. In practical, you  should list the columns that you want to get data explicitly because of the following reasons:

• The asterisk (*) 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 application.
• If you explicit specify the columns, the result set is more predictable and easier to manage. Imagine when you use the asterisk(*) and someone changes the table by adding more columns, you will end up with a result set that is different from what you expected.
• Using asterisk (*) may expose sensitive information to unauthorized users.

In this tutorial, we’ve learnt about the MySQL SELECT statement to query data from a table. Now we’ll learn MySQL DISTINCT in next chapter.

---

Load Sample Database MySQL DISTINCT

---