Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

SQL in Adaptive Server

The relational operations

The relational operations

The basic query operations in a relational system are selection (also called restriction), projection, and join. These can all be combined in the SQL select command.

A selection is a subset of the rows in a table. You specify the limiting conditions in the select query. For example, you might want to look only at the rows for all authors who live in California.

select * 
from authors 
where state = "CA" 

A projection is a subset of the columns in a table. For example, this query displays only the name and city of all authors, omitting the street address, the phone number, and other information.

select au_fname, au_lname, city
from authors

A join links the rows in two or more tables by comparing the values in specified fields. For example, suppose you have one table containing information about authors, including the columns au_id (author identification number) and au_lname (author's last name). A second table contains title information about books, including a column that gives the ID number of the book's author (au_id). You might join the authors table and the titles table, testing for equality of the values in the au_id columns of each table. Whenever there is a match, a new row--containing columns from both tables--is created and displayed as part of the result of the join. Joins are often combined with projections and selections so that only selected columns of selected matching rows display.

select * 
from authors, publishers
where authors.city = publishers.city