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 on a regular basis. 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 select 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)
SQL SELECT - All Employees

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 use 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 rarely get the data from all columns of a table. In fact, you need only a subset of columns. To do that, you need to specify the 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)
SQL select 1 column

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

SELECT lastName, firstName FROM employees
Code language: SQL (Structured Query Language) (sql)
SQL select more than 1 column

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 displays employee’s full name:

SELECT CONCAT(LastName,', ',FirstName) AS fullname FROM employees
Code language: SQL (Structured Query Language) (sql)
SQL SELECT with CONCAT function

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.