Selecting specific columns from a table

You can limit the columns that a SELECT statement retrieves by listing the 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 columns and table you are querying.

For example:

SELECT Surname, GivenName
FROM Employees;

Projections and restrictions

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).

Rearranging the order of columns

The order in which you list 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;

DepartmentID	DepartmentName
100	R & D
200	Sales
300	Finance
400	Marketing
...	...

SELECT DepartmentName, DepartmentID
FROM Departments;

DepartmentName	DepartmentID
R & D	100
Sales	200
Finance	300
Marketing	400
...	...

Joins

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.

See Joins: Retrieving data from several tables.

Discuss this page in DocCommentXchange.