SQL SELECT

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

When working with a database, querying data from a table is one of the most common tasks that you have to deal with regularly. To query data from one or more tables, you use the SELECT statement.

The basic syntax of SQL SELECT statement

The following illustrates the basic syntax of the SELECT statement:

SELECT select_list
FROM table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, the SELECT statement has two clauses: SELECT and FROM.

• First, specify one or more column names after the SELECT keyword.
• Second, specify the name of the table from which you want to retrieve data.

Typically, the SELECT statement only requires the SELECT clause. The FROM clause is optional. If you don’t specify the FROM clause, the SELECT statement will not select data from any table.

The SELECT statement returns data including rows and columns, which is often referred to as a result set.

Using the SQL SELECT statement to select all data from a table

Before querying data from a table, you need to know the table from which you want to get data. After that, you can specify the table name from the table name in the FROM clause.

The following example uses the SELECT statement to query data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

In this query, you put the employees table after the keyword FROM, and asterisk character ( *) after the SELECT keyword.

The query returns data from all rows and columns in the employees table. The asterisk ( *) character is the shorthand for all columns.

The following query is equivalent to the one above but uses explicit column names:

SELECT EmployeeID,
       LastName,
       FirstName,
       Title,
       TitleOfCourtesy,
       BirthDate,
       HireDate,
       Address,
       City,
       Region,
       PostalCode,
       Country,
       HomePhone,
       Extension,
       Photo,
       Notes,
       ReportsTo,
       PhotoPath
FROM employees;Code language: SQL (Structured Query Language) (sql)

Using the SELECT statement to select a subset of columns in a table

In practice, you will rarely retrieve all data from a table. Instead, you may need only a subset of columns. To do that, you specify the specific columns in the SELECT clause.

The following query uses the SELECT statement to get all the last names from the lastname column in the employees table:

SELECT lastname 
FROM employees;Code language: SQL (Structured Query Language) (sql)

To specify multiple columns, you use a comma (,) like this:

SELECT lastName,
       firstName
FROM employees;Code language: SQL (Structured Query Language) (sql)

Using the SQL SELECT statement with other elements

Besides the table columns, you can use the following elements in the SELECT clause:

• Strings or Numbers
• Expressions
• SQL functions
• User-defined function.

For example, the following query uses the SELECT statement with a simple expression:

SELECT 1 + 1;Code language: SQL (Structured Query Language) (sql)

This example shows you how to combine a string function named CONCAT(), which joins two or more strings into one, to display the employee’s full name:

SELECT CONCAT(LastName,', ',FirstName) AS fullname
FROM employeesCode language: SQL (Structured Query Language) (sql)

An SQL alias is used to format the output in this example.

Summary

• Use the SELECT statement to query data from a table.
• Specify one or more column names after the SELECT clause to which you want to query data.
• Specify the name of the table from which you want to query data.