• Home
• FAQs
• Forums
• Links
• Online Tools
• phpBB
• Scripts
• Tutorials:
• Apache
• CSS
• Databases
• Graphics
• Hosting
• HTML & XHTML
• Miscellaneous
• Perl
• PHP

• About us
• Contact us
• Credits
• Link to us

Write a tutorial for us
Bookmark this tutorial

Print this tutorial
Comment on this tutorial

The Anatomy of a SQL Statement: Part 1: SELECT FROM

The purpose of this tutorial is to break down the basics of a SQL SELECT statement. I intend for this to be the start of a series of hopefully interesting, entertaining, and just plain useful tutorials on relational databases and how they work, and how to work with them.

In The Beginning, The World Was Flat

First of all, just what is SQL? SQL stands for Structured Query Language. It was developed to manipulate data stored in a relational database structure. Prior to this concept most data files were stored as "flat files" and read in sequential order. That meant that in order to find a particular record, you either needed to read the entire file throwing away data you didn't want, or know the exact offset into the file. Think of this like playing a VCR tape. Unless you know the exact counter value where your taped copy of "Coupling" is, you have to fast forward through the tape looking for it. Time consuming, isn't it?

Contrast that with a DVD broken down into chapters. You have the ability to select the exact area of the disk you want to read. Much more efficient, isn't it! Certainly makes it easier to use. Relational databases solve a similar problem. I don't want to have to know where the data is, I just want to be able to select it. And that gets us to the point of the tutorial.

The Language of SQL

There are two types of SQL statements, DDL and DML. DDL stands for Data Definition Language, and includes language used to define the database structure. DDL includes things like

CREATE DATABASE foo...
CREATE TABLE bar...
CREATE INDEX bar_PK...

... and so on. We'll talk about those in another tutorial. DML stands for Data Manipulation Language, and is much more interesting. With DML you can insert, update, delete, or... yes, even SELECT data from your relational database. The SELECT statement is what you use to retrieve data from your database, afterall, if it was important enough to store, you might want it back and sometime, right?

The most basic select statement that can be written is

SELECT *
FROM foo

That's it. By the way, for this (and any future) tutorials, reserved words (words that are part of the SQL language) will be written in ALL CAPS in code segments, and other words will be in lower case. Now that we have that out of the way, what does this statement do?

Suppose you have a table named "foo" in your database. This table stores a number of interesting attributes about one of your customers. Well, in that case, the table is probably called customers, so we'll use that from now on. (What's a table? Another tutorial, where we get to talk about entities and relationships and attributes and stuff.) So to retrieve your customer information, you would write:

SELECT *
FROM customers

What does the * do? Well, in this case it seems that we're too lazy to specify exactly what sort of customer information we want, so we're asking for everything. The * (sometimes called "splat", probably because it looks like a bug on your windshield) is a shortcut that allows you you ask for everything from the table.

What if you only wanted the first and last name for your customer, as well as their phone number? In that case you could be more specific, as in:

SELECT cust_fname, cust_lname, phone
FROM customers

The "splat" has been replaced by a number of columns from the table. Instead of viewing the entire customer row, you've been more selective. See how that works? Each attribute (column) that you want to retrieve from the table is listed, separated by commas. Now, what about those names? "cust_fname" is a bit cryptic, don't you think? Oh sure, you could probably figure it out. But why not rename it with an alias?

SELECT cust_fname AS Customer_First_Name, cust_lname AS Customer_Last_Name, phone AS Phone_Number
FROM customers

This is an optional step, but if you're dealing with complex (or obtuse) result values, an alias can be a big help. It can certainly help make your PHP code easier to read later on.

So now you know how to retrieve customer information from your database table. What if your boss comes by and wants to know exactly how many customers are in the database? You don't have to retrieve every customer and count them, simply use the COUNT() function instead. That is still something you're selecting from the database, so it goes like this:

SELECT COUNT(*)
FROM customers

Remember the splat? (That's the * if you were paying attention earlier.) Since we don't really care to retrieve anything, this is a shorthand way to ask for a count of records from the customer table. If you have a large number of customers, a more efficient way to count them would be this:

SELECT COUNT(cust_id)
FROM customers

This assumes that each customer has been assigned a unique customer id (called cust_id) and that this value is mandatory (not null) in the database. Why would this be faster? Without going into too much detail, suffice it to say that counting the entire customer record can require retrieving the full 1,000 bytes from the database, while counting the unique ID counts just that one column. Depending on how your database is tuned, it may not even use the table at all, but count the INDEX instead. (What's an INDEX? Yet another tutorial subject...)

The COUNT() function is one of several group functions available in a SQL database. What if you wanted the first (oldest) and last (most recent) customer dates? That might look like this:

SELECT MIN(cust_add_date) AS First_Customer_Date, MAX(cust_add_date) AS Most_Recent_Customer_Date
FROM customers

Yes, you can use the same customer information more than once. In this case, the results of this SELECT statement will be a single row of data that will include the date that the very first customer was added, as well as the date the most recent customer was added. Note the use of an alias to make the expression more specific? Always a good idea. We'll cover these special functions in much more depth in a later tutorial when we cover the GROUP BY clause of a SELECT statement.

What if we want to return data from more than one table at a time? What if we want to restrict data from the customer table to rows that match certain criteria? What if... well, that's the subject for the next tutorial: The Anatomy of a SQL Statement: Part 2: WHERE, coming soon.

© 4WebHelp and Dave