3.3.1. Simple Queries

SELECT column, column, ..., column
FROM table

or:

SELECT *
FROM table

which will query and fetch back all the columns within the specified table.

Therefore, to select some of the rows from some columns in the megaliths table, the following SQL statement can be used:

SELECT name, location, mapref
FROM megaliths

which would return the following information:

+---------------------------------------------------------------+
| name         | location                          | mapref     |
+---------------------------------------------------------------+
| Callanish I  | Callanish, Isle of Lewis          | NB 213 330 |
| Lundin Links | Lundin Links, Fife, Scotland      | NO 404 027 |
| Stonehenge   | Near Amesbury, Wiltshire, England | SU 123 400 |
| Avebury      | Avebury, Wiltshire, England       | SU 103 700 |
| Sunhoney     | Near Insch, Aberdeenshire         | NJ 716 058 |
+---------------------------------------------------------------+

So even with the simplest SQL imaginable, the inherent flexibility of the syntax allows us to easily specify exactly which information we want from the database without having to write lots of excruciating lines of code to get it.

Another aspect of the relational database methodology is now visible, in that even though the database contains information on all the columns within a particular table, only a subset of the available columns needs to be retrieved. Therefore, we can extract exactly the data we need for a particular query and no more. This is an extremely powerful feature and separates the actual data stored within the database from our desired view of that data.

3.3.2. Queries and Condition Clauses

SQL's syntax regarding condition clauses is just as straightforward and obvious as that for specifying which columns are of interest. The condition clauses that narrow the query are specified after the list of tables from which data is being retrieved, i.e., after the FROM clause and table list.

Therefore, a query that retrieves the name and location columns from rows that contain the string ``Wiltshire'' in the location column, can be written as:

SELECT name, location
FROM megaliths
WHERE location LIKE '%Wiltshire%'

The information returned from this query would be:

+--------------------------------------------------+
| name         | location                          |
+--------------------------------------------------+
| Stonehenge   | Near Amesbury, Wiltshire, England |
| Avebury      | Avebury, Wiltshire, England       |
+--------------------------------------------------+

SELECT name, location
FROM megaliths
WHERE location LIKE '%Wiltshire%'
AND mapref LIKE 'SU 123%'

In this example, the second condition is simply added to the end of the list of conditions that must be met. The two conditions are joined together by a logical operator, AND. This statement now reads "Give me the name and location of all megalithic sites with a location of Wiltshire that are in the map region SU 123," which would return the name and location fields for the "Stonehenge" row, but reject the "Avebury" row.

The following table describes the boolean (or logical) operators defined by SQL that can be used to chain your condition clauses together.

The truth of the overall condition is determined by combining the truth of each element separately using the AND, OR, and NOT operators.

It is now possible to calculate the effects of multiple condition clauses in a statement. For example, the following condition clauses:

WHERE location LIKE '%Wiltshire%'
AND mapref LIKE 'SU 123%'

evaluate in the following way for this row:

+----------------------------------------------------------+
| name          | mapref     | location                    |
+----------------------------------------------------------+
| Avebury       | SU 103 700 | Avebury, Wiltshire, England |
+----------------------------------------------------------+

location LIKE '%Wiltshire%'     => TRUE
mapref LIKE 'SU 123%'           => FALSE

TRUE AND FALSE => FALSE

thereby returning a false value for that row, causing it to be rejected by the query. However, the following row:

+---------------------------------------------------------------+
| name         | mapref     | location                          |
+---------------------------------------------------------------+
| Stonehenge   | SU 123 400 | Near Amesbury, Wiltshire, England |
+---------------------------------------------------------------+

evaluates as:

location LIKE '%Wiltshire%'     => TRUE
mapref LIKE 'SU 123%'           => TRUE

TRUE AND TRUE => TRUE

which ensures that the row is returned by the query.

For example, you might wish to select all the megalithic sites in either ``Wiltshire'' or ``Fife'' for which the description of the site contains the word ``awe-inspiring.''

This query could be wrongly expressed as:

SELECT name, location
FROM megaliths
WHERE location LIKE '%Wiltshire%' OR location LIKE '%Fife%'
AND description LIKE '%awe-inspiring%'

While this query looks correct at first, it does not take into account the order in which the condition clauses are combined. It would, in fact, select the awe-inspiring sites in Fife, but it would also select all the sites in Wiltshire regardless of their type.

This happens because the AND operator has a higher precedence than the OR operator and so is evaluated first. Therefore, our SQL statement evaluates by AND combining location LIKE %Fife% and description LIKE %awe-inspiring%. It then OR combines the result of the AND operation with location LIKE %Wiltshire%. This isn't quite what we had in mind.

This query can be more correctly written by using parentheses to logically group operators within the statement.

For example:

SELECT name, location
FROM megaliths
WHERE ( location LIKE '%Wiltshire%' OR location LIKE '%Fife%' )
AND description LIKE '%awe-inspiring%'

SELECT name, description
FROM megaliths
WHERE name IN
    ( SELECT tourist_sitename
      FROM wiltshire_tourist_sites )

3.3.3. Queries over Multiple Tables

To illustrate this concept, it is time to reintroduce the other tables we shall be using in our examples, namely the media table and site_types table.

The media table contains information on where multimedia clips for given sites can be located, allowing an external application to view or listen to these clips while the user is reading the textual information on the site stored in the megaliths table.

Similarly, the site_types table contains a lookup table of the different categorizations of megalithic sites described within the database.

To specify a SELECT statement from two or more tables in the database, we simply add the table names after the FROM keyword. Therefore, a sample query to fetch all the rows in two of the tables should theoretically look something like:

SELECT name, description, location, url, content_type
FROM megaliths, media

How do you make sure that the values in the fields from the second table related to the values from the first -- that is, that the media clips for ``Stonehenge'' are only returned with the ``Stonehenge'' site information?

In our megaliths table, we have already defined a column called id that contains a unique identifier for each and every row stored within the table. Similarly, the media table has a column called id that performs the same purpose. Furthermore, the media table also contains a column called megaliths_id. When a row is inserted into the megaliths or media tables, a unique identifier is inserted into the id columns. Also, when a row is inserted into the media table, the megaliths_id column is populated with the unique identifier of the megalithic site to which the media clip relates.

We can now write a query to fetch the appropriate information back from the database by joining the two tables on their related fields. This ensures that the media clips are associated with the correct site:

SELECT name, description, url, content_type
FROM megaliths, media
WHERE megaliths.id = megaliths_id

This illustrates another aspect of SQL conditions: instead of testing arbitrary values against columns in a table, it is possible to test against the value of another column. In the above case, we test to see if the primary and foreign keys of the two tables match, and, if so, the aggregated row created from the columns of both tables is returned.

Also note how we qualified the id field name in the condition clause by prepending it with the table name and a dot. Without that, the database would not have known if we were referring to the id field in the megaliths table or the media table and would fail with an error.

Similarly, if we wished to select the id fields of both tables, the following statement would simply confuse the database, and we'd get another error:

SELECT id, id, megaliths_id
FROM megaliths, media
WHERE id = megaliths_id

Therefore, it is good practice to explicitly specify the table name that the field belongs to, in cases where it may be ambiguous. For example:

SELECT megaliths.name, megaliths.description, 
       media.url, media.content_type
FROM megaliths, media
WHERE megaliths.id = media.megaliths_id

SELECT mega.name, mega.description, med.url, med.content_type
FROM megaliths mega, media med
WHERE mega.id = med.megaliths_id

It is more common just to use the initial character of the table name provided the aliases are unique.

For example, fetching both the media associated with a site and the site type information can be expressed with the following query:

SELECT mega.name, mega.description, st.site_type,
       med.url, med.description
FROM megaliths mega, media med, site_types st
WHERE mega.id = med.megaliths_id
AND mega.site_type_id = st.id

For example, we might wish to retrieve information on all of the sites located within Wiltshire and, if any exist, the URLs of any multimedia clips associated with them. Using a simple join such as:

SELECT mega.name, mega.location, med.url
FROM megaliths mega, media med
WHERE mega.id = med.megaliths_id
AND mega.location LIKE '%Wiltshire%'

would return only those sites in Wiltshire that had media clips associated with them. It would exclude those sites that had no media clips. An outer join is the way to solve this problem.

For example, the standard query to retrieve the information we desired can be written as:

SELECT mega.name, mega.location, med.url
FROM megaliths mega
     LEFT OUTER JOIN media med ON mega.id = med.megaliths_id
WHERE mega.location LIKE '%Wiltshire%'

In this example, we have made a left outer join on the id and megaliths_id columns because for any sites without media clips, there are no corresponding records in the media table. The left outer join will ensure that even if no media clip records exist, at least the name and location of each and every site in Wiltshire will be returned. A right outer join in this query would have returned the values where no entries in the megaliths table existed.

Finally, it is worth mentioning some ways to make efficient table joins. In our examples, we added additional columns to our tables to make a join between them. We could have simply added a column to the media table that contained the name of the megalithic site.

There are a few good reasons why we didn't do that:

1. If the name of the megalithic site was updated for some reason -- for example, if a spelling mistake needed correcting -- the name contained within the media table would be out of date and incorrect. This would break the join between the two tables for that particular row.

2. Integer keys use much less space than strings when building indexes on a table. Less space means more index entries per block of disk space, and therefore fewer disk reads. The smaller index and fewer disk reads make up for the slightly increased size of the master data table, and usually give you both speed and space gains.

3. It is slower to test strings than to test numbers, especially integers. As such, in a well-designed database, integers are often used for primary and foreign keys because they are faster to test against with comparison operators. A string, on the other hand, requires testing of every character within each string, which can be time-consuming.

3.3.4. Grouping and Ordering Data

Often you'll desire a little more control over how your selected data is retrieved. The two most common ways of organizing your data are to order the retrieved rows by one or more columns, or to group the retrieved rows and apply functions to the groups instead of to individual rows.

Perl is well-suited to these tasks within your program, but performing ordering and grouping via SQL will offload the task onto the database server and also will save you writing, or using, potentially suboptimal techniques for organizing the data. Therefore, generally, use SQL rather than your own application-level code.