Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Selecting specific columns from a table

You can limit the columns that a SELECT statement retrieves by listing the desired column(s) immediately after the SELECT keyword. This SELECT statement has the following syntax:

SELECT column-name [, column-name ]...
FROM table-name

In the syntax, column-name and table-name should be replaced with the names of the desired columns and table you are querying.

The list of result set columns is called the select list. It is separated by commas. There is no comma after the last column in the list, or if there is only one column in the list. Limiting the columns in this way is sometimes called a projection.

For example:

SELECT Surname, GivenName
FROM Employees;

A projection is a subset of the columns in a table. A restriction (also called selection) is a subset of the rows in a table, based on some conditions.

For example, the following SELECT statement retrieves the names and prices of all products in the SQL Anywhere sample database that cost more than $15:

SELECT Name, UnitPrice
FROM Products
WHERE UnitPrice > 15

This query uses both a projection (SELECT Name, UnitPrice) and a restriction (WHERE UnitPrice > 15).

The order in which you list the column names determines the order in which the columns are displayed. The two following examples show how to specify column order in a display. Both of them find and display the department names and identification numbers from all five of the rows in the Departments table, but in a different order.

SELECT DepartmentID, DepartmentName
FROM Departments

SELECT DepartmentName, DepartmentID
FROM Departments

A join links the rows in two or more tables by comparing the values in columns of each table. For example, you might want to select the order item identification numbers and product names for all order items that shipped more than a dozen pieces of merchandise:

SELECT SalesOrderItems.ID, Products.Name
FROM Products JOIN SalesOrderItems
WHERE SalesOrderItems.Quantity > 12

The Products table and the SalesOrderItems table are joined together based on the foreign key relationship between them.

For more information about using joins, see Joins: Retrieving Data from Several Tables.