Subsets of Rows and Columns

Selecting subsets of rows and/or columns is one of the most common operations we will carry out in a query. In the following sections, we will look at selecting rows and columns from one of the original tables in the database.¹ The same ideas apply to retrieving information from virtual tables that result from other manipulations of the data.

To determine which rows to retrieve from a table, it is necessary to specify a condition, which is a statement that is either true or false. We apply the condition to each row in the table independently, retaining those rows for which the condition is true and discarding the others. Say we want to find all the seniors in the golf club. We want just that subset of rows from the Member table where the value in the MemberType field is “Senior,” as shown in Figure 2-1.

Figure 2-1. Retrieving the subset of rows for Senior members .

The SQL for the query to retrieve Senior members is as follows:

SELECT *
FROM Member
WHERE MemberType = 'Senior'

This query has three parts, or clauses: The SELECT clause says what columns to retrieve. In this case, * means retrieve all the columns. The FROM clause says which table(s) the query involves, and the WHERE clause describes the condition for deciding whether a particular row should be included in the result. The condition says to check the value in the field MemberType. In SQL, when we specify an actual value for a character or text field, we need to enclose the value in single quotes, as in 'Senior'.

Now let’s look at how we can specify that we want to see only some of the columns in our result. I will generally refer to selectinga subset of rows and projectinga subset of columns. Often the projection of a subset of columns is the last step in a series of operations. We can think of gathering all the data we require and then at the end asking for just the attributes or columns we need. We will see in Chapter 7 that we sometimes also need to project similar columns from original or virtual tables before applying some of the set operations, such as union and intersection.

If we want a phone list of all the members we don’t need extra information such as handicaps or join dates. Figure 2-2 show a subset of the name and phone number columns from the Member table.

Figure 2-2. Projecting a subset of columns to provide a phone list

The SQL to retrieve the name and phone columns from the Member table is:

SELECT LastName, FirstName, Phone
FROM Member

Because we want to see these column values for every row, this query doesn’t have a WHERE clause .

It is a simple matter to combine the retrieval of subsets of rows and columns. We might do this if we wanted a phone list for just the senior members, as in Figure 2-3.

Figure 2-3. Retrieving a subset of rows and columns to produce a phone list of Senior members

The SQL for the query depicted in Figure 2-3 is:

SELECT LastName, FirstName, Phone
FROM Member
WHERE MemberType = 'Senior'

Using Aliases

As our queries get more complicated they will incorporate a number of different tables. Some of the tables may have the same column names, and we might need to distinguish them from each other. In SQL we can preface each of the attributes in our query with the name of the table that it comes from, as shown here:

SELECT Member.LastName, Member.FirstName, Member.Phone
FROM Member
WHERE Member.MemberType = 'Senior'

Because typing the whole table name can become tiresome, and also because in some queries we might need to compare data from more than one row of a table, SQL has the notion of an alias. Have a look at the following query:

SELECT m.LastName, m.FirstName, m.Phone
FROM Member m
WHERE m.MemberType = 'Senior'

In the FROM clause, we have declared an alias or alternative name for the Member table, in this case m. We can give our alias any name or letter we like; shorter is better. Then, in the rest of the query we can use the alias whenever we want to specify an attribute from that table. It is a good idea to get into the habit of using a table alias for each table contributing to the query.

Saving Queries

It is possible to keep the result of a query in a new permanent table (sometimes called a snapshot), but we usually don’t want to do that because it will become out of date if the underlying data changes. What we usually want to do is save the query instructions so that we can ask the same question another day. Consider our phone list query. Every so often after the membership of the club has been updated, we will produce a new phone list. Rather than having to construct the query each time, we can save the instructions in what is known as a view. The code below shows how to create a view that we can use to provide up-to-date phone lists. We have to give the view a name, which can be anything we want (PhoneList seems sensible), and then we supply the SQL statement for retrieving the appropriate data:

CREATE VIEW PhoneList AS
SELECT m.LastName, m.FirstName, m.Phone
FROM Member m

You can think of PhoneList as the instructions to create a “virtual” table that we can use in other queries in the same way that we use real tables. We just need to remember that the virtual table is created on the fly by running the query on the permanent Member table and it is then gone. To get our phone list now, we can simply use the PhoneList view:

SELECT * FROM PhoneList

Specifying Conditions for Selecting Rows

In the queries we looked at in the previous sections, we used very simple conditions or criteria for determining whether to include a row in the result of a query. In the following section, we will look more closely at the different ways you can specify more complicated conditions.

SELECT *
FROM Member m
WHERE UPPER(m.MemberType) = 'JUNIOR'

Logical Operators

We can combine Boolean expressions to create more interesting conditions. For example, we can specify that two expressions must both be true before we retrieve a particular row.

Let’s assume we want to find all the junior girls. This requires two conditions to be true: they must be female, and they must be juniors. We can easily express each of these conditions independently. After that, we can use the logical operator AND to require that both conditions be true:

SELECT *
FROM Member m
WHERE m.MemberType = 'Junior' AND m.Gender = 'F'

We will look at three logical operators: AND, OR, and NOT. We have already seen how AND works. If we use OR between two expressions, then only one of the expressions need be true (but if they are both true, that is OK as well). NOT is used before an expression. For example, for our Member table, we might ask for rows obeying the condition NOT (MemberType = 'Social'). This means check each row, and if the value of MemberType is “Social,” then we do not want that row. Table 2-3 gives some more examples of using logical operators in conditions.

Table 2-3. Examples of Logical Operators

Expression	Description of Data
MemberType = 'Senior' AND Handicap < 12	Seniors with a handicap under 12
MemberType = 'Senior' OR Handicap < 12	All the senior members as well as anyone else with a good handicap (those less than 12)
NOT(MemberType = 'Social')	All the members except the social ones (for the current data, that would be just the seniors and juniors)

Figure 2-4 shows a diagrammatic representation of the queries in Table 2-3. Each circle represents a set of rows (that is, those for social members or those for members with handicaps under 12). The shaded area represents the result of the operation.

Figure 2-4. Diagrammatic representation of the logical operators.

The truth tables in Figure 2-5 can be helpful in understanding how the logical operators work. You read them like this: in Figures 2-5a and 2-5b, we have two expressions, one along the top and one down the left. Each expression can have one of two values: True (T) or False (F). If we combine them with the Boolean expression AND, then Figure 2-5a shows that the overall statement is true only if both the contributing statements are true (the square in the top left). If we combine them with an OR statement, then the overall statement is false only if both contributing statements are false (bottom right of Figure 2-5b). The table in Figure 2-5c says that if our original statement is true and we put NOT in front, then the result is false (left column), and vice versa.

Figure 2-5. Truth tables for logical operators (T = true, F = false)

Sometimes it can be a bit tricky turning natural-language descriptions into Boolean expressions. If you were asked for a list that included all the women and all the juniors (don’t ask why!), you might translate this literally and write the condition MemberType = 'Junior' AND Gender = 'F'. However, the AND means both conditions must be true, so this would give us junior women. What our natural-language statement really means is “I want the row for any member if they are either a woman or a junior (or both).” Be careful.

Dealing with Nulls

The example data in the Member table shown earlier in Figure 2-1 is all accurate and complete. Every row has a value for each attribute, except for Handicap, which doesn’t apply to some members. Real data is usually not so clean and tidy. Let’s consider some different data, as in Figure 2-6.

Figure 2-6. Table with missing data

When there is no value in a cell in a table, it is said to be null. Nulls in a database can cause a few headaches. Consider carrying out the following two queries : one to produce a list of male members and the other a list of females. Given that golfers need to identity as either male or female for competition purposes, we might assume that all the members of the club would appear on one list or the other. However, for the data in Figure 2-6, we would leave out Kim Spence. You could argue that the data shouldn’t be like that, but we are talking about real people and real clubs with less than accurate and complete data. Maybe Kim forgot (or refused) to fill in the gender part of the application form. We can protect against this by insisting that nulls are not allowed in a particular field when we create a table. The following SQL statement shows how we could make Gender a field that always requires a value:

CREATE TABLE Member (
MemberID INT PRIMARY KEY,
.....
Gender CHAR(1) NOT NULL,

....)

It is worth bearing in mind that making fields NOT NULL can create more headaches than it cures. If Kim Spence did not complete all the boxes on his/her membership application but had organized payment for the subscription, then we want to record him/her as a member and worry about the full details later. If we make Gender a required field, then we can’t enter a record for him/her in the table—or we have to guess what his/her gender is. Neither of these options is a good strategy, so it is best to be sparing when making fields required. Remember that our primary key fields (by definition) always need a value.

Not all values of null mean there is a problem with the data. In our Member table, a field might be null because it does not apply to a particular member. Helen and Sarah’s handicap may be genuinely null because they do not have handicaps. However, it is fair to assume that every member should have a value for MemberType and JoinDate, so the nulls in these columns are because we do not know the value. In the real world, expect that your tables will have missing data.

SELECT *
FROM Member m
WHERE m.Gender IS NULL

SELECT *
FROM Member m
WHERE NOT (m.Handicap IS NULL)

Comparisons Involving Null Values

Given that we are going to have unexpected nulls in our tables, it is important to know how to deal with them. What rows will match the two conditions shown here?

Gender = 'F'
NOT (Gender = 'F')

You might think that if we carry out two queries, one to get all the rows that match a condition and another for all the rows that don’t match, then we will get the whole table. But, in fact, we don’t. Kim will not be included with the first condition, because clearly the value of Gender does not equal 'F'. But when we ask whether the value is NOT 'F' we can’t say, because we don’t know what the value is. It might be 'F' if it had a value. In SQL when we compare null values with something, we don’t get either True or False because we simply don’t know. This probably makes more sense if we think about handicaps. If we ask for everyone with Handicap > 12, and also for those members who satisfy either NOT (Handicap > 12) or Handicap <=12, then Sarah’s row will never be retrieved. The question doesn’t apply to her — she doesn’t have a handicap.

Once we take nulls into consideration, our expressions for conditions might actually have one of three values: True, False, or “Don’t know.” That is pretty much how the world works, if you think about it. Only rows that are True for a condition are retrieved in a query. If the condition is False or if we don’t know, then the row is not retrieved.

If we include “Don’t know” in the truth tables they will look like those in Figure 2-7. For an AND operation, if one expression is False, then it doesn’t matter about the others — the result will be False. For an OR operation, if one expression is True, then it doesn’t matter about the others, so the result will be True.

Figure 2-7. Truth tables with three-valued logic (T = True, F = False, ? = Don’t know)

Managing Duplicates

If our tables have been designed well, they will have a primary key. This ensures that every row is unique. However, as soon as we retrieve a subset of data from the tables the result may not have unique rows.³ Let’s look at an example.

Consider retrieving just the FirstName column from the Member table. Figure 2-8 shows two possible results.

Figure 2-8. Projecting the FirstName column from the Member table

It is useful to think about why we might carry out a query retrieving just names. Perhaps the query is to help prepare a set of nametags for a club party. If that is the case, then two Thomases and a William are going to feel left out if we use the unique output.

You might think, what’s all the fuss? Of course we want to keep all the rows. However, consider retrieving just the column with the membership types. Figure 2-9 shows the outputs with duplicates included and removed.

Figure 2-9. Projecting the MemberType column from the Member table

It’s pretty difficult to think of a situation where you want the duplicated rows in Figure 2-9a. The two operations we have considered sound similar in natural language. “Give me a list of first names” and “Give me a list of membership types” sound like the same sort of question, but they mean quite different things. The first means “Give me a name for each member,” and the other means “Give me a list of unique membership types.”

What does SQL do? If we say SELECT MemberType FROM Member, we will get the output in Figure 2-9a with all the duplicates included. If we do not want the duplicates, then we can use the keyword DISTINCT:

SELECT DISTINCT m.MemberType
FROM Member m

Whether or not you keep the duplicates depends very much on the information you require, so you need to give it careful thought. If you were expecting the set of rows in Figure 2-9b and got Figure 2-9a, you would most likely notice. With the two sets of rows in Figure 2-8, it is much more difficult to spot that you have perhaps made a mistake. Get into the habit of thinking about duplicates for all your queries.

Ordering Output

Every now and then I refer to a “set of rows” rather than a table or a virtual table. The word set has two implications. One is that there are no duplicates (and we have discussed that a lot!). The other implication is that there is no particular order to the rows in our set. In theory, we don’t have a first row or a last row or a next row. If we run a query to retrieve all the rows, or just some of the rows, from a table, then we have no guarantee in what order they will be returned. However, sometimes we might like to display the results in a particular order. We can do this with the key phrase ORDER BY. The following shows how to retrieve member information ordered alphabetically by LastName:

SELECT *
FROM Member m
ORDER BY m.LastName

We can order by two or more values. For example, if we want to order Senior members with the same LastName by the value of their FirstName, we can include those two attributes (in that order) in the ORDER BY clause:

SELECT *
FROM Member m
WHERE m.MemberType = 'Senior'
ORDER BY m.LastName, m.FirstName

The type of a field determines how the values will be ordered. By default, text fields will be ordered alphabetically, number fields will be ordered numerically (smallest first), and date and time fields chronologically (earlier dates and times first). We can also specify that the order be reversed with the keyword DESC (for descending). There is an equivalent keyword ASC (for ascending), which is the default if neither is specified. The following will return member names and handicaps ordered in descending order; i.e., with the highest value of handicap first:

SELECT m.Lastname, m.FirstName, m.Handicap
FROM Member m
ORDER BY m.Handicap DESC

The way nulls are ordered in any output depends on the application; you will need to check. For example, in SQL Server and Microsoft Access, nulls will appear at the top of an ascending list and the bottom of a descending list. Oracle provides keywords such as NULLS FIRST and NULLS LAST so you can choose where the null values go. A little trick to get your nulls at the bottom of an ascending list in SQL Server is to use a case statement:

SELECT m.LastName, m.FirstName, m.Handicap
FROM Member m
ORDER BY (CASE
             WHEN m.Handicap IS NULL THEN 1
             ELSE 0
          END), m.Handicap

The preceding query has two attributes in the ORDER BY clause. It orders firstly by the case statement in the parentheses. You can think of the case statement as creating a virtual column giving the value 0 to those rows with a handicap and 1 to those which have no handicap value. When we order by this first attribute in the ORDER BY clause, the rows with a value for a handicap will be before the nulls. Within these groups the rows will then be ordered by the value of the handicap in ascending order.

Performing Simple Counts

As well as retrieving a subset of rows and columns from a table, we can also use SQL queries to provide some statistics. There are SQL functions that allow us to count records, total or average values, find maximum and minimum values, and so on. In this section, we will look at some simple queries for counting records. We will return to this topic in Chapter 8.

We can use the COUNT function to return the number of records in the Member table. In the following query, * means count each record:

SELECT COUNT(*) FROM Member

We can also count a subset of rows by adding a WHERE clause to specify those rows we want to include. For example, we can use the following query to count the number of senior members:

SELECT COUNT(*) FROM Member m
WHERE m.MemberType = 'Senior'

Because we have just been talking about nulls and duplicate values, it is worth briefly mentioning here how these will affect our counts. Rather than use * as a parameter to the COUNT function so that it counts all the rows, we can put an attribute such as Handicap in the parentheses. If we do this only those rows with a value in the Handicap field will be included in the count.

SELECT COUNT(Handicap) FROM Member

We can also specify that we want to count the number of unique values for an attribute. If we want to know how many different values of MemberType appear in the Member table then we can use the following query:

SELECT COUNT(DISTINCT MemberType) FROM Member

It is worth reiterating that different database software will support different parts of the SQL standard syntax. For example, Microsoft Access currently does not support COUNT(DISTINCT MemberType), seen in the previous query. There is usually a way to work around these differences to find an equivalent query, and we will look at how to rephrase the preceding query and other issues related to aggregates and summaries in Chapter 8.

Avoiding Common Mistakes

Retrieving a subset of rows and columns from a single table is the most simple of SQL queries. However, you have seen that you still need to be careful. It is important to remember that there will be null values in your tables and to think carefully about how your selection conditions will treat them. You also need to remember that if you do not retain the primary key fields from your tables, there is the potential to have duplicate rows, and you must deal with them appropriately.

There are a couple of other mistakes that are commonly made when selecting a subset of rows. They don’t become apparent with a table like Member, so I’ll introduce some more of the tables in the golf club database. Figure 2-10 shows part of the Member table and two other tables: Entry and Tournament. The first row in the Entry table records that person 118 (Melissa McKenzie) entered tournament 24 (Leeston) in 2014.

Figure 2-10. Introducing the Tournament and Entry tables

We can use some of the SQL operations we have already seen on the Entry table to answer questions such as which tournaments (just the TourID number) person 258 has entered, who (just the MemberID number) has ever entered tournament 24, or who entered tournament 36 in 2015. The following is the SQL for the last query:

SELECT e.MemberID
FROM Entry e
WHERE e.TourID = 36 AND e.Year = 2015

Incorrectly Using a WHERE Clause to Answer Questions with the Word “both”

In the previous section we used the logical operator AND to find rows in the Entry table where both TourID = 36 and Year = 2015 were true.

Say we wanted to find the members who have entered both tournaments 36 and 38. There is a temptation to again use the AND operator and write the query as follows:

SELECT e.MemberID
FROM Entry e
WHERE e.TourID = 36 AND e.TourID= 38

Can you work out what this query will return? This is where it is helpful to think in terms of the row variable e investigating each row in table Entry as in Figure 2-11.

Figure 2-11. The row variable e investigates each row independently.

Imagine our finger is pointing at the row shown in the diagram. Does this row (415, 36, 2015) satisfy the condition e.TourID = 36 AND e.TourID= 38? It satisfies the first part, but the AND operator requires the row to satisfy both conditions. No single row in our table will have both 36 and 38 in the tournament column because each row is for just one entry. The SQL query we suggested will never find any rows; it will always return an empty table. If we change the Boolean operator to OR, we will get the row indicated in Figure 2-10 returned; however, we will also then get anyone who has entered either 36 or 38 but not necessarily both.

This particular query cannot be solved with a simple WHERE clause. By definition, the condition in the WHERE applies to each row independently. To answer the question about who has entered both competitions, we need to look at more than one row of the Entry table at the same time (that is, two fingers). If we have two fingers, one pointing at the row shown in Figure 2-10 and another pointing at the following row, then we can deduce that 415 has entered both tournaments. We’ll look at how to do this in Chapter 5.

SELECT e.MemberID
FROM Entry e
WHERE e.TourID <> 38

Summary

In this chapter, we have looked at queries on a single table. Some of the main points covered are:

• We can return a subset of rows that satisfy a given condition by using a WHERE clause. The condition is a Boolean expression, which is a statement that is either true or not true. The condition is applied to each row of the table independently.

• The SELECT clause allows us to specify a subset of columns.

• Because the result of a query is a set of rows, we cannot guarantee the order in which the rows will be returned. If we want to display the result in a particular order, we can use the ORDER BY clause.

• It is possible to create a view, which essentially stores an SQL command so that you can run it over and over again as the data in the base tables change.

• Tables are likely to have null values (both on purpose and by mistake). Always check how your conditions will apply to null values.

• When you project a subset of columns using an SQL command, the default is to retain duplicate rows in the result. Always think about how you need to deal with the duplicates, and use the keyword DISTINCT if you want unique rows.

• The WHERE clause considers only one row at a time. Don’t use it for queries that require you to look at several rows at once, as in who entered both tournaments or who did not enter this tournament.