Part of the
You Can Learn SQL series.
By Ken Brown
Editor, YouCanLearnSeries.com
Updated: October 14, 2004
The SELECT statement retrieves information from the database. You can select
information from one row or many rows, one field or column or many columns and
from one table or many tables.
The SELECT statement is the workhorse of SQL commands and allows you to present
data in many different formats to the user that requested the data.
SELECT * FROM Employees
This statement returns all rows and all columns from one table with the name of
the table called "Employees" and is the basic SELECT statement. The asterik *
represents the keyword ALL and is standard in the database community for
returning all rows and all columns from a table or multiple tables.
To limit the amount of data returned you can begin by limiting the columns that
you are selecting. As an example,
SELECT lastname, firstname, title FROM Employees
will return only the lastname, firstname and title information from the table.
To further limit the information retrieved from the database you can add
qualifiers to the SELECT statement. A qualifier looks at the table you are
querying and only returns the information that meets that qualifier. So if you
have a table with a column called title. And the information in that table also
contained fields called lastname and firstname, then you could write a query
whereby you only returned lastname and firstname in rows that had a specific
title such as "Sales Representative". Write the query as:
SELECT lastname, firstname, title
FROM Employees
WHERE title = 'Sales Representative'
You would get a list of lastname,firstname,and title, that were only contained
in the rows with a title = 'Sales Representative'.
It is also important to be able to get information from multiple tables at the
same time. The SELECT statement can meet your need to query more than one
table. When you query two tables at the same time you are performing a JOIN. I
will explain in additional articles the ins and outs of joins and how to return
the specific info you want. JOINS can be a complicated subject and database
professionals can spend hours creating, testing and honing a SELECT statement
with multiple tables before being satisfied with the resultset returned. Here
is a simple example to familiarize you with the basics.
Notice in the sample there are two tables in the FROM clause, "Order Details",
Orders. It is a requirement that you list both tables with a comma between
them. Then in the WHERE clause you have to show what field is common between
the two tables. In our example, the common field is the "OrderID". But if you
show the common field as "OrderID" = "OrderID" you will get an error,
"Ambiguous column name 'OrderID'". This is bad and to repair the problem you
need to list the table name before the column name. You see "Orders.OrderID"
where the table name precedes the column name followed by the period between
the table name and column name.
We used the asterik * to bring back ALL the rows and columns of a table. But
you may want to only bring back certain columns. So you repeat the steps from
above where we selected a specific column name we wanted returned. You must
again add the table name before any column names that are duplicated between
tables, otherwise you receive this error, "Ambiguous column name 'OrderID'."
This article was intended to teach you the fundamentals of the SELECT
statement. How to return all the rows and all columns from a table. How to
return specific columns and how to qualify which rows are returned from a
query. If it didn't meet all those needs, please send an email so we can
improve upon this article.
Happy programming.
|