The SELECT Statement

Part of the You Can Learn SQL series.
By Ken Brown
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.

SELECT * FROM employees

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.

SELECT lastname, firstname, title FROM Employees

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

SELECT * FROM employees WHERE 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.

Shows a select statement with a join of 2 tables

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'."

Shows the need to add a table name with a dot then the column name, otherwise you get an error of 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.

Home| About Us | NewsLetters | Contact Us |

Copyright © 2004-2014 You Can Learn Series

You Can Learn Series Home Page You Can Learn Series
You Can Learn Series Home Page Home            About the creators of YouCanLearnSeriesAbout Us      Contact YouCanLearnSeriesContact Us      Site map of YouCanLearnSeriesSite Map      Privacy Policy of You Can Learn SeriesPrivacy Policy       Terms of Use of You Can Learn Series web siteTerms of Use       YouCanLearnSeries RSS feed for SQL tips and tutorials     
C# and ASP.Net Programming TipsC# and ASP.Net      SQL Tutorial, tips and tricks for DBA'sSQL       Landscaping Tips for Home GardenersLandscaping       WeightLifting Tips for the Young and OldWeightLifting       Good health TipsGoodHealth       Learn about Great Travel DestinationsTravel     Web Business TipsWeb Business     

You Can Learn C# and ASP.Net SeriesC# and ASP.Net

You Can Learn SQL SeriesSQL
  Data Definition LanguageData Definition Language
  Data Manipulation LanguageData Manipulation Language
  What is a DatabaseWhat is a Database
  What is SQLWhat is SQL
  SELECT StatementSELECT Statement
  INSERT StatementINSERT Statement
  DELETE StatementDELETE Statement
  UPDATE StatementUPDATE Statement

You Can Learn Landscaping and Gardening SeriesLandscape Tips
You Can Learn WeightLifting SeriesWeightLift Tips
You Can Learn Good Health SeriesGood Health Tips
You Can Learn Series NewslettersNewsletters
Learn about Great Travel DestinationsTravel
You Can Learn Series Web Business TipsWeb Business Tips

Bill Welter - The Prepared Mind of a Leader

Americas Public Schools