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 ]...
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.
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
|100||R & D|
SELECT DepartmentName, DepartmentID FROM Departments
|R & D||100|
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.