SQL SELECT

Summary: in this tutorial, you will learn how to use the SQL SELECT statement to query data from a single table.

Introduction to SQL SELECT statement

To query data from a table, you use the SQL SELECT statement. The SELECT the statement contains the syntax for selecting columns, selecting rows, grouping data, joining tables, and performing simple calculations.

The SELECT the statement is one of the most complex commands in SQL, therefore, in this tutorial, we’ll focus on the basics only.

The following illustrates the basic syntax of the SELECT a statement that retrieves data from a single table.

SELECT 
    select_list
FROM
    table_name;

In this syntax:

• First, specify a list of comma-separated columns from which you want to query the data in the SELECT clause.
• Then, specify the table name in the FROM clause.

When evaluating the SELECT the statement, the database system evaluates the FROM clause first and then the SELECT clause.

The semicolon (;) is not part of a query. It is used to separate two SQL queries. Check out the SQL syntax for more information.

In case you want to query data from all columns of a table, you can use the asterisk (*) operator, like this:

SELECT * FROM table_name;

Notice that SQL is case-insensitive. It means that the SELECT and select keywords are the same.

To make the SQL statements more readable, we will use the uppercase letters for the SQL keywords such as SELECT and FROM and the lowercase letters for the identifiers such as table and column names.

Besides the SELECT and FROM clauses, the SELECT the statement can contain many other clauses such as

•  WHERE – for filtering data based on a specified condition.
•  ORDER BY – for sorting the result set.
•  LIMIT – for limiting rows returned.
•  JOIN – for querying data from multiple related tables.
•  GROUP BY – for grouping data based on one or more columns.
•  HAVING – for filtering groups.

You will learn about these clauses in the subsequent tutorials.

SQL SELECT statement examples

We’ll use the employees table in the sample database for demonstration purposes.

SQL SELECT – querying data from all columns

To query data from all columns of a table, you use an asterisk (*) rather than listing all the column names.

The following example retrieves data from all the columns of the employees table:

SELECT * FROM employees;

The result set contains the data of the columns in the order in which they were defined when the  employees the table was created:

Using the asterisk (*) operator is only convenient for querying data interactively through an SQL client application.

However, if you use the asterisk (*) operator in the embedded SQL statements in your application, you may have some potential problems.

The reason is that the table structure will evolve to adapt to the new business requirements e.g., you may add a new column or remove an existing column. If you use the asterisk (*) and don’t change the application code to make it work with the new table structure, the application may not work properly.

On top of this, using the asterisk (*) might cause a performance issue. The application often doesn’t need all data from all the columns of a table. If you use the asterisk (*), the database server has to read the unnecessary data and this unnecessary data has to transfer between the server and application. It causes slowness in the application.

SQL SELECT – querying data from specific columns

The SELECT the statement allows you to specify exactly which columns you want to retrieve data in any order. It doesn’t have to be in the order defined in the table.

For example, if you use want to view the employee id, first name, last name, and hire date of all employees, you use the following query:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees;

Notice that the result set includes only four columns specified in the SELECT clause.

SQL SELECT – performing a simple calculation

As mentioned earlier, the SELECT statement allows you to perform simple calculations.

For example, the following query calculates the year of services of employees on January 1st, 2016 using the FLOOR() ,DATEDIFF() and CURRENT_DATE functions:

SELECT 
    employee_id,
    first_name,
    last_name,
    FLOOR(DATEDIFF('2016-01-01', hire_date) / 365) YoS
FROM
    employees;

The following shows the output at the time of running this query. If you execute the query, you will get a higher YoS because the current date is always after

The CURRENT_DATE the function returns the current date and time. The DATEDIFF() function returns the difference in days between the hire date and the current date.

To calculate the year of service, we divide the result of the DATEDIFF() function by 365.

The FLOOR() the function returns the largest integer less than or equal to the result of a numeric expression.

The YoS is the column alias for the expression below to display a user-friendly heading in the returned result set.

FLOOR(DATEDIFF('2016-01-01', hire_date) / 365)

Note that this query works in MySQL. If you use SQL Server, you can use the following query:

SELECT 
    employee_id,
    first_name,
    last_name,
    DATEDIFF(year, hire_date, '2016-01-01')
FROM
    employees;

For SQLite, you use the following query. This query also works on the SQL Online Tool.

SELECT 
    employee_id,
    first_name,
    last_name,
    FLOOR(julianday('2016-01-01') - julianday(hire_date)) / 365 YoS
FROM
    employees;

Now, you should know how to use the SQL SELECT statement to query data from a single table.