SELECT Queries

A SELECT query is built up of a number of individual parts. Each of these parts is called a clause. By combining the required clauses, we can extract data from the database.

In the examples below, we write our queries over multiple lines. This does not need to be done and the spacing is just to make the queries easier to read. We will also use an example table with some pets in it to demonstrate some of the ways we can use queries. In proper form, all queries should end with a semi-colon, although not many database engines enforce that requirement.

The 'Pets' table:

In this table, each pet is given a unique number to identify it (the field ID). This is the Primary Key of the table.

Basic Structure of a Query

One of the simplest queries that we can write is to extract specific columns from the database. The query has two clauses: the SELECT and the FROM clauses:

SELECT <field(s)>

FROM <table>;

In the SELECT clause, we list each of the fields that we want extracted and in the FROM clause we say which table the data is going to come from.

SELECT PetsName, Age

FROM Pets;

The results of this query would be displayed as follows:

Some other points to note about the field names are that they are do not contain any spaces. It is possible to write queries with fields that contain spaces, but you should be careful to find out how your database needs these to be written. In Microsoft Access, for example, the field needs to be enclosed in []s and in MySQL, the field name needs to be contained in ``s (on the same key as the "~" on your keyboard).

In Access:

SELECT Name, Age, [Postal Address]

FROM Employees;

Or in MySQL:

SELECT Name, Age, `Postal Address`

FROM Employees;

To keep things simple, we will always use tables that do not have spaces in the field names.

One other point that we can make about a query, specifically about the field list, is that we can prefix the fields with the table name. In this case, there is really no point, but in some cases we need to get information from more than one table at a time and we will need to be able to distinguish which tables the fields actually come from. The query above could just have easily been written like this:

SELECT Pets.PetsName, Pets.Age

FROM Pets;

Getting all the fields

Very often, we need to extract all the fields in a query. To do this, we can list each of the fields individually - but this takes time. Instead, we can replace the list of fields with an asterisk (*) as shown in the example below:

SELECT *

FROM Pets;

We can also use the table name as a prefix:

SELECT Pets.*

FROM Pets;

The results of these two queries are identical and shown below:

Changing the order of the results

Now that we can fetch a list of data from the database, we can manipulate it in various methods. The simplest way is to sort it. We tell the database which fields to sort the data by using an ORDER BY clause. Sorting is the very last process that happens when generating a list of query results and so the process is saved until last. As such, it appears last in the query:

SELECT <field(s)>

FROM <table>

ORDER BY <field(s)>;

As an example:

SELECT PetsName

FROM Pets

ORDER BY PetsName;

By default, SQL will sort text fields into alphabetical order, number fields into numerical order and date & time fields into oldest to newest. It is possible to reverse the order of the sorting by adding the "DESC" modifier to the ORDER BY clause:

SELECT PetsName, Age

FROM Pets

ORDER BY Age DESC;

You will have seen in the original listing of the clause above, that the ORDER BY clause can take more than one field. What happens if we provide more than one field? In this case, the database would sort according to the first field and, if there were two that had the same sorting value, it could then use the next field to determine which order they should go in. For example:

SELECT PetsName, Age

FROM Pets

ORDER BY Age DESC, PetsName;

Now, where two pets have the same age (Spot and Topsy, Hobo and Puddles, and Polly and Pinky do) the database would ensure that their names were sorted into alphabetical order. In the list above, this means that "Polly" and "Pinky" would swap places because those names are not in alphabetical order.

Filtering the rows

Frequently, we do not want all the rows to be returned. We are interested in a smaller subset of the data. We can include this into our query using a WHERE clause.

Our generic query now becomes:

SELECT <field(s)>

FROM <table>

WHERE <condition(s)>

ORDER BY <field(s)>;

Note, at this point that while we list all of the clauses above, not all of them are required for a valid query and we can omit and include the ones we want, as we need them. An example of a WHERE clause follows, this time without the ORDER BY clause:

SELECT PetsName, Age

FROM Pets

WHERE Age > 4;

The conditions that we use are similar to those we use in programming:

- = - equal to
- <> - not equal to
- > - greater than
- < - less than
- >= - greater than or equal to
- <= - less than or equal to

SQL provides us an additional comparative operator: LIKE. This allows us to perform some pattern matching using wildcards. In Access, the wildcard is a "*" and in MySQL, the wildcard is "%".

SELECT PetsName, Age

FROM Pets

WHERE PetsName LIKE 'P*';

This query also provides an interesting example of a String literal. Here it is enclosed in 'single quotes'. This will come as no surprise to Delphi programmers, but Java programmers might be a little alarmed to see it. The good news is that the quotes can be interchanged and either can be used. This query could just as easily have been written as follows:

SELECT PetsName, Age

FROM Pets

WHERE PetsName LIKE "P*";

In addition to the logical conditions that we can use, we can also use logical operators to combine these conditions. The three logical operators are the same as in programming: AND, OR and NOT. Here are some examples of logical operators:

SELECT PetsName, Age

FROM Pets

WHERE Type = 'Dog' AND Innoculated = true;

Note also, the use of the Boolean literal in this query. In the table, the tickbox field is a simple representation of a boolean field. A ticked box means "true" and an unticked box is "false".

This query also demonstrates an interesting point in that the field "Innoculated" is used in the WHERE clause but is not listed in the SELECT clause. That is no problem at all!

Here is an example of an OR operation:

SELECT PetsName, Age, Innoculated

FROM Pets

WHERE Innoculated = true OR Age < 2;