Basic MySQL Usage

NOTE

As the name of this section implies, the following discussion should be taken as a very basic introduction to the MySQL server. Because entire books have been devoted to the discussion of all the functionality of SQL and MySQL, it is important to realize that only the fundamental principals of RDBMS/SQL as it pertains to MySQL will be discussed in this book. If you are interested in learning SQL and MySQL in more detail, I recommend visiting the MySQL online documentation at http://www.mysql.com/ or reading the book MySQL, Second Edition by Paul DuBois, published by Developer's Library.

RDBMS Fundamentals

To work with databases from within PHP, you first need to understand how to work with the database itself. For those with absolutely no RDBMS experience, let's start by introducing you to the fundamental principals of relational databases. In RDBMS packages, data is organized in the following fashion:

Each RDBMS consists of one or more database(s).
Data within each database is organized into one or more table(s).
Tables are organized into rows and columns.
Every "column" represents an individual piece of data of a given type for a given record.
Every "row" represents a single database record.

Without prior experience, how this organizational structure is used to manage your data can be somewhat elusive. However, in reality, this model makes logical sense. To illustrate this concept, let's look at an example of when a database might be useful. In this example, I will be creating a database that holds some quotes that I enjoy from famous people:

"Do, or do not. There is no 'try'."
Yoda

"Knowledge speaks, but wisdom listens."
Jimi Hendrix, Musician

"I would have made a good Pope."
Richard M. Nixon

Looking at this data, you can see that it is divided into two separate fields. The first field (which I will label "quote") contains the quote itself, and the second field (which I will label "author") is the famous person who said it. Using these two labels, we can define a table structure I'll call myquotes to hold the data:

Figure 23.1. A table containing author and quote.

This concept of storing data within a table structure is exactly the concept used with RDBMS databases such as MySQL. In this case, our database contains a single table, myquotes, which is defined with two columns named quote and author. In this case, this particular table contains a total of three records (rows).

At this point we have described a perfectly good database management system; however, it is not yet a relational database management system. As the definition of the word implies, a relational database must relate one thing to another. For instance, let's create a second table called occupation that keeps track of the occupation of each person in the myquotes table as shown in Figure 23.2:

Figure 23.2. A table containing author and occupation.

Although two entirely different tables, there is now a relation between the occupation and myquotes tables in the database through the common column author. Note that this is not an explicit relationship (meaning the database software itself knows nothing of this relationship); however, because the author column exists in both tables, there is an implicit association between the two. For instance, you can now relate a given quote with an occupation as shown in Figure 23.3.

Figure 23.3. Relating two tables to simulate a third using a common column.

[View full size image]

As you will see shortly, this concept of relations between tables is a cornerstone of any RDBMS system.

Performing Queries Using SQL

When manipulating a database or retrieving information from it in some form or another, the Structured Query Language (SQL) must be used. All RDBMS database packages support SQL, including the MySQL database. Although the details of the SQL implementation between software packages may differ slightly, this section can be taken as a fairly universal guide to any SQL implementation.

To begin this section, you'll need to be logged in to the MySQL server using the mysql client:

[user@localhost]# mysql -uunleashed -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.12-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

At this point you are ready to issue queries to the database server and retrieve or manipulate information. When working directly from the MySQL client, as we are in this section, all queries must be terminated by the semicolon character (;) to indicate the end of the query. As you will see when we discuss using SQL from within PHP scripts, the semicolon is unnecessary (and, in fact, not allowed). Like any programming language, SQL can do calculations, call functions, have variables, and so on. To use this functionality, let's introduce the SELECT statement, which can be used in the following fashion:

mysql> SELECT 2 + 2;
+-------+
| 2 + 2 |
+-------+
|     4 |
+-------+
1 row in set (0.17 sec)

NOTE

As is the case throughout this chapter, I will not be providing the formal statement syntax for SQL functions such as SELECT. Because these statements can be incredibly complex, such information would be of little educational value for an introduction to SQL. Check the online documentation at http://www.mysql.com/ if the formal syntax is desired.

Beyond simple math, the SELECT statement can also be used to call functions such as VERSION() or NOW(), which return the MySQL version and the current time, respectively:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 4.0.12-standard |
+-----------------+
1 row in set (0.09 sec)
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2003-04-17 05:16:27 |
+---------------------+
1 row in set (0.03 sec)

These two SELECT statements can also be combined into a single statement as shown:

mysql> SELECT VERSION(), NOW();
+-----------------+---------------------+
| VERSION()       | NOW()               |
+-----------------+---------------------+
| 4.0.12-standard | 2003-04-17 05:18:08 |
+-----------------+---------------------+
1 row in set (0.01 sec)

As you can see, results retrieved from a SQL query will always be presented in a table form. In the preceding example, we have asked MySQL to return a table with two columns. The first column should be the result from the VERSION() function, whereas the second is the result from the NOW() function.

Finally, queries can also span multiple lines, as illustrated by the following example:

mysql> SELECT 2+2,
    -> VERSION(),
       -> NOW();
      +-----+-----------------+---------------------+
      | 2+2 | VERSION()       | NOW()               |
      +-----+-----------------+---------------------+
      |   4 | 4.0.12-standard | 2003-04-17 06:06:28 |
      +-----+-----------------+---------------------+
      1 row in set (0.00 sec)

Now that you have a feel for how to perform queries against a MySQL database (or at least call functions, which is a similar process) it's time to create a database on which to perform some real manipulations. This is done by using the CREATE statement in the following fashion:

mysql> CREATE DATABASE unleashed;
Query OK, 1 row affected (0.02 sec)

NOTE

Depending on your permissions, you may or may not be able to create databases on the MySQL server. Many ISPs provide a precreated database that can be used. If you are unable to create a database, the one that was provided to you will suffice.

Now that you have created a database, you must set it as the active database by using the USE statement:

mysql> USE unleashed;
Database changed

At this point, you have an empty database named unleashed and can begin creating tables within it. As you may recall from earlier in this chapter, I defined a table as a group of columns of a specific type. To create a table, you must first know the nature of the data that will be stored in that table. Following is a list of the more common data types available in MySQL:

INT[(D_SIZE)] [UNSIGNED] [ZEROFILL]
An integer value between 2147483648 and 2147483647 if signed or between zero and 4294967295 if unsigned. If the ZEROFILL attribute is provided, the number will be prefixed with zeros if it contains less than D_SIZE digits.
VARCHAR[(D_SIZE)] [BINARY]
A variable-length string of size D_SIZE with a maximum of 255 characters. Unless the BINARY attribute is present, the string is considered not case sensitive.
TEXT
A case-sensitive string with a maximum of 65,535 characters.
ENUM('value_1', 'value_2', ..., NULL)
An enumeration whose acceptable values are strings contained within the list (that is, 'value_1', and so on). A maximum of 65,535 unique values may be specified for each enumeration.
DATE
A date in the format YYYY-MM-DD ranging from 1000-01-01 to 9999-12-31.
DATETIME
A date and time ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. All DATETIME values are represented in YYYY-MM-DD HH:MM:SS format.

NOTE

The preceding list introduces only a few of the more common MySQL data types. A complete list can be found in the MySQL documentation at http://www.mysql.com/.

When creating a table within a database, each column must be given both a name and assigned a valid data type. Recalling the quotes database I described earlier in the chapter, the following is a CREATE statement example to create the myquotes table:

mysql> CREATE TABLE myquotes(quote TEXT, author VARCHAR(255));
Query OK, 0 rows affected (0.02 sec)

To check the properties of our newly created table, the DESCRIBE statement can be used:

mysql> DESCRIBE myquotes;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| quote  | text         | YES  |     | NULL    |       |
| author | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

As you can see, the DESCRIBE statement provides a means to determine the specific details of each column within the specified table. The DESCRIBE statement is extremely useful if you forget the details regarding a specific column within a table or if you need to know the order in which the data is stored within the table. This order is important, as you will see when adding new records using the INSERT statement.

Along the same lines as DESCRIBE, the SHOW statement provides a list of all the tables or databases managed by the MySQL server:

mysql> SHOW DATABASES;
+-----------+
| Database  |
+-----------+
| mysql     |
| test      |
| unleashed |
+-----------+
3 rows in set (0.02 sec)
mysql> SHOW TABLES;
+---------------------+
| Tables_in_unleashed |
+---------------------+
| myquotes            |
+---------------------+
1 row in set (0.03 sec)

NOTE

You may have noticed that two additional databases are shown when the SHOW DATABASES statement was executed (the mysql and test databases). In a standard installation of MySQL, the mysql database contains settings for the MySQL server itself, and the test database is simply a test database example to play with.

Referring back to the DESCRIBE example, you'll notice that the Null column of the myquotes table is set to 'Yes' for both the quote and author columns (which is also the default value for both specified by the Default column). This means that MySQL will allow you to use the special value NULL instead of a real string for each of these column types. Because this does not make a great deal of sense for our purposes, it would be nice if we could make the following changes to our table:

Forbid either the quote or author column from being NULL.
Make the default value of the author column 'Unknown Author' instead of NULL.

To implement these changes into our table, we must either modify the already existing table or create a new table. Because there is no data in the myquotes table yet, it's probably easier just to delete that table instead of modifying it by using the DROP statement:

mysql> DROP TABLE myquotes;
Query OK, 0 rows affected (0.01 sec)

NOTE

Be very careful when dropping tables! After a table has been dropped, it cannot be recovered and all the data will be lost.

To implement the changes to our table, we must return to the CREATE TABLE statement and introduce two qualifiers that can be used when specifying columns. The first of these qualifiers is NOT NULL, which will indicate that this column must contain a non-NULL value, and the second is DEFAULT, which can be used to specify a default value:

mysql> CREATE TABLE myquotes(
     -> quote TEXT NOT NULL,
     -> author VARCHAR(255) NOT NULL DEFAULT "Unknown Author");
Query OK, 0 rows affected (0.00 sec)

To verify the changes to our table, examine the output from the DESCRIBE statement for the table:

mysql> DESCRIBE myquotes;
+--------+--------------+------+-----+----------------+-------+
| Field  | Type         | Null | Key | Default        | Extra |
+--------+--------------+------+-----+----------------+-------+
| quote  | text         |      |     |                |       |
| author | varchar(255) |      |     | Unknown Author |       |
+--------+--------------+------+-----+----------------+-------+
2 rows in set (0.00 sec)

While we are creating tables, let's also create the occupation table, which will store the occupation of each quote author. This table will have two columns, author and occupation, as defined by the following CREATE statement:

mysql> CREATE TABLE occupation(
    -> author VARCHAR(255) NOT NULL,
    -> occupation VARCHAR(255) DEFAULT "Unknown Occupation");
Query OK, 0 rows affected (0.00 sec)

To make these tables useful, they must contain some data to manipulate. To load data into these tables, the INSERT statement must be used. For instance, to load the first quote from earlier in the chapter into the myquotes table, the following INSERT statement would be used:

mysql> INSERT INTO myquotes VALUES("Do, or do not. There is no 'try'.", "Yoda");
Query OK, 1 row affected (0.02 sec)

As I indicated when I discussed the DESCRIBE statement, the order in which values are stored is important. Because the table was defined with the quote column first, followed by the author column, this is the way it must be inserted via this form of the INSERT statement.

If, for whatever reason, a given piece of data is not available, the special value DEFAULT may be specifiedin which case the default value will be used. The following SQL statements will add two new quotes to the myquotes table; in both cases the default value for author will be used:

mysql> INSERT
     > INTO myquotes
     > VALUES("To not risk is to know not sadness or joy", NULL);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT
     > INTO myquotes (quote)
     > VALUES("Character is much easier kept than recovered");
Query OK, 1 row affected (0.00 sec)

In the first of the preceding two INSERT queries, note the use of the DEFAULT special value to instruct MySQL to use the default value for that column. Similarly, the second query accomplishes the same result, except that instead of specifying the DEFAULT value explicitly, it is implied by excluding the column name from the INSERT statement entirely.

To finish our discussion of the INSERT statement, the following lines will populate the remainder of the database by adding records to both the myquotes and occupation tables:

mysql> INSERT
     > INTO myquotes
     > VALUES("Knowledge speaks, but wisdom listens.", "Jimi Hendrix");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT
     > INTO myquotes
    > VALUES("I would have made a good Pope.", "Richard M. Nixon");
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO occupation VALUES("Yoda", "Jedi Master");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO occupation VALUES("Jimi Hendrix", "Musician");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT
     > INTO occupation
     > VALUES("Richard M. Nixon", "Former President");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO occupation VALUES("Unknown Author", DEFAULT);
Query OK, 1 row affected (0.01 sec)

Now that we have a database example populated with some data, we can start querying the database for specific pieces of information. All queries that retrieve information regarding the actual content of a table are done via the SELECT statement. For instance, to retrieve a complete listing of all the data in the myquotes database, the following statement would be used:

mysql> SELECT * FROM myquotes;
+----------------------------------------------+------------------+
| quote                                        | author           |
+----------------------------------------------+------------------+
| Do, or do not. There is no 'try'.            | Yoda             |
| To not risk is to know not sadness or joy    | Unknown Author   |
| Character is much easier kept than recovered | Unknown Author   |
| Knowledge speaks, but wisdom listens.        | Jimi Hendrix     |
| I would have made a good Pope.               | Richard M. Nixon |
+----------------------------------------------+------------------+
5 rows in set (0.00 sec)

Examining the preceding query, you see that the SELECT statement can be translated roughly into Select everything from the table 'myquotes'.

To retrieve a list of authors in the myquotes table, the following query could be used:

mysql> SELECT author FROM myquotes;
+------------------+
| author           |
+------------------+
| Yoda             |
| Unknown Author   |
| Unknown Author   |
| Jimi Hendrix     |
| Richard M. Nixon |
+------------------+
5 rows in set (0.01 sec)

As is shown, in this case only the specified column author was requested. As was the case earlier in the chapter when functions were used instead of column names, multiple individual columns can be selected from a table by separating each with a comma.

NOTE

This method also can be used to change the order in which data is presented in the resultset. For example, the following query will display the column author first, followed by the respective quote:

mysql> SELECT author, quote FROM myquotes;

Column names can also be renamed in the resultset in the following manner:

mysql> SELECT author as author_name FROM myquotes;
+------------------+
| author_name      |
+------------------+
| Yoda             |
| Unknown Author   |
| Unknown Author   |
| Jimi Hendrix     |
| Richard M. Nixon |
+------------------+
5 rows in set (0.00 sec)

As is shown, instead of the original column name author, you also can specify an alias. This is particularly useful when dealing with column names generated by a function, because by default MySQL's column name in those situations will be identical to the function call itself.

When performing queries, at times it would be useful to have the resulting set returned in a sorted fashion. To facilitate this, SQL provides the ORDER BY clause, which enables you to order the results of a query by a specific column in ascending or descending order. For instance, an alphabetical list of the quote authors can be retrieved by the following query:

mysql> SELECT author FROM myquotes ORDER BY author;
+------------------+
| author           |
+------------------+
| Jimi Hendrix     |
| Richard M. Nixon |
| Unknown Author   |
| Unknown Author   |
| Yoda             |
+------------------+
5 rows in set (0.01 sec)

To order the list in reverse (descending) order, specify the DESC keyword following the sorting column:

mysql> SELECT author FROM myquotes ORDER BY author DESC;
+------------------+
| author           |
+------------------+
| Yoda             |
| Unknown Author   |
| Unknown Author   |
| Richard M. Nixon |
| Jimi Hendrix     |
+------------------+
5 rows in set (0.00 sec)

When you're using the ORDER BY clause, functions can be used as well as columns. For example, you could randomize the resultset by using the RAND() function instead of a column name:

mysql> SELECT * FROM myquotes ORDER BY RAND();
+----------------------------------------------+------------------+
| quote                                        | author           |
+----------------------------------------------+------------------+
| Knowledge speaks, but wisdom listens.        | Jimi Hendrix     |
| Do, or do not. There is no 'try'.            | Yoda             |
| Character is much easier kept than recovered | Unknown Author   |
| I would have made a good Pope.               | Richard M. Nixon |
| To not risk is to know not sadness or joy    | Unknown Author   |
+----------------------------------------------+------------------+
5 rows in set (0.01 sec)

NOTE

Because we are ordering by a random value, it is very likely the result of the previous query will be different for you than what is shown here.

Thus far, all the queries requesting data from a given table have returned at least a portion of every single record. In most cases, only a subset of the entire table should be returned based on a certain criteria. In SQL, this criteria is defined by the use of the WHERE clause in the SELECT statement. For instance, to return only those quotes for which the author was unknown, the following is used:

mysql> SELECT quote FROM myquotes WHERE author = "Unknown Author";
+----------------------------------------------+
| quote                                        |
+----------------------------------------------+
| To not risk is to know not sadness or joy    |
| Character is much easier kept than recovered |
+----------------------------------------------+
2 rows in set (0.00 sec)

Notice that in this case, only two of the five records were returned from the myquotes table. In this case, we have limited the results by specifying a criteria that must be met for the record to be returned in the resultset. MySQL supports the following comparison operations:

A = B
True if A equals B
A != B
True if A does not equal B
A <= B
True if A is less than or equal to B
A >= B
True if A is greater than or equal to B
A < B
True if A is less than B
A > B
True if A is greater than B
A <=> B
True if A is equal to B (NULLsafe)
A IS NULL
True if A is NULL
A IS NOT NULL
True if A is not NULL
A BETWEEN M AND N
True if A is between the values of M and N
A NOT BETWEEN M AND N
True if A is not between the values of M and N
A IN (value, ...)
True if A is one of the values in the list provided
A NOT IN (value, ...)
True if A is not one of the values in the list provided

NOTE

When comparing two potentially NULL values, it is important to use the NULL safe comparison operator. By definition, the result of the comparison NULL = NULL is false because NULL is undefined. To compare to NULL values and have it be a true comparison, the <=> operator must be used instead of =.

Beyond the preceding set of comparison operations, MySQL also allows comparisons to be done according to wildcards or regular expressions. To match against a general wildcard, the LIKE subclause is used as follows:

mysql> SELECT * FROM myquotes WHERe author LIKE "%x%";
+---------------------------------------+------------------+
| quote                                 | author           |
+---------------------------------------+------------------+
| Knowledge speaks, but wisdom listens. | Jimi Hendrix     |
| I would have made a good Pope.        | Richard M. Nixon |
+---------------------------------------+------------------+
2 rows in set (0.00 sec)

In the preceding example, the LIKE subclause is used to retrieve all the quotes from authors who have the letter X in their name. A similar query could be used to find all the author names that start with the letter U:

mysql> SELECT * FROM myquotes WHERE author LIKE "U%";
+----------------------------------------------+----------------+
| quote                                        | author         |
+----------------------------------------------+----------------+
| To not risk is to know not sadness or joy    | Unknown Author |
| Character is much easier kept than recovered | Unknown Author |
+----------------------------------------------+----------------+
2 rows in set (0.00 sec)

Likewise, regular expressions can also be utilized by using the REGEXP subclause, as shown next, which is functionally identical to the previous example:

mysql> SELECT * FROM myquotes WHERE author REGEXP "^u";
+----------------------------------------------+----------------+
| quote                                        | author         |
+----------------------------------------------+----------------+
| To not risk is to know not sadness or joy    | Unknown Author |
| Character is much easier kept than recovered | Unknown Author |
+----------------------------------------------+----------------+
2 rows in set (0.01 sec)

Resultsets also can be limited to a certain range of records as well through the use of the LIMIT clausefor instance, to return only the first three records of the myquotes table, as shown next:

mysql> SELECT * FROM myquotes ORDER BY author LIMIT 3;
+----------------------------------------------+----------------+
| quote                                        | author         |
+----------------------------------------------+----------------+
| Do, or do not. There is no 'try'.            | Yoda           |
| To not risk is to know not sadness or joy    | Unknown Author |
| Character is much easier kept than recovered | Unknown Author |
+----------------------------------------------+----------------+
3 rows in set (0.00 sec)

To return a specific range of records, the LIMIT clause also can specify a starting location in the resultset, such as the following (which returns two records starting from the third record in the resultset):

mysql> SELECT * FROM myquotes ORDER BY author LIMIT 3, 2;
+---------------------------------------+------------------+
| quote                                 | author           |
+---------------------------------------+------------------+
| Knowledge speaks, but wisdom listens. | Jimi Hendrix     |
| I would have made a good Pope.        | Richard M. Nixon |
+---------------------------------------+------------------+
2 rows in set (0.00 sec)

A useful application of the LIMIT clause is to combine it with the ORDER BY using the RAND() function to pick a random result from the resultset:

mysql> SELECT * from myquotes ORDER BY RAND() LIMIT 1;
+-------------------------------------------+----------------+
| quote                                     | author         |
+-------------------------------------------+----------------+
| To not risk is to know not sadness or joy | Unknown Author |
+-------------------------------------------+----------------+
1 row in set (0.00 sec)

Beyond limiting and ordering resultsets, resultsets (or specific columns within them) can also be tallied. For instance, the total number of rows in the resultset can be determined using the COUNT() function as shown:

mysql> SELECT COUNT(*) FROM myquotes;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

In general, it is not necessary to perform a query such as the preceding one, because PHP provides (at least when using MySQL) functions that perform the same task. However, the COUNT() function has other uses, such as counting the total instances of a particular value within a column. To generate such a query, yet another clause, GROUP BY, must be used. This clause will instruct MySQL to display only one of each value for the specified column(s). Consider the following query:

mysql> SELECT * FROM myquotes GROUP BY author;
+-------------------------------------------+------------------+
| quote                                     | author           |
+-------------------------------------------+------------------+
| Knowledge speaks, but wisdom listens.     | Jimi Hendrix     |
| I would have made a good Pope.            | Richard M. Nixon |
| To not risk is to know not sadness or joy | Unknown Author   |
| Do, or do not. There is no 'try'.         | Yoda             |
+-------------------------------------------+------------------+
4 rows in set (0.00 sec)

Recall from earlier in the chapter that there should be two quotes for which the author column has the value "Unknown Author". However, because the GROUP BY clause was specified, only one (the first one in the resultset) was actually provided. When you couple it with a function such as COUNT(), you can create queries that, for instance, report the number of quotes by each author:

mysql> SELECT author, COUNT(author) as totals FROM myquotes GROUP by author;
+------------------+---------------+
| author           | totals        |
+------------------+---------------+
| Jimi Hendrix     |             1 |
| Richard M. Nixon |             1 |
| Unknown Author   |             2 |
| Yoda             |             1 |
+------------------+---------------+
4 rows in set (0.00 sec)

As has been mentioned numerous times, MySQL (and all SQL-based databases) are relational databases designed to relate data found in one table with the data found in another. Recall from earlier in the chapter that the unleashed database contains two separate tables, myquotes and occupation, representing famous quotes and the occupation of their authors, respectively. To illustrate the power of relational databases, consider the possible methods that could be used to show each famous quote together with the occupation of its author. In a situation like this, RDBMS software packages such as MySQL show their true power by allowing you to merge two different tables into a single table based on an arbitrary criteria, as shown in the following query:

mysql> SELECT myquotes.quote, occupation.occupation
    -> FROM myquotes, occupation
    -> WHERE myquotes.author = occupation.author;
+----------------------------------------------+--------------------+
| quote                                        | occupation         |
+----------------------------------------------+--------------------+
| Do, or do not. There is no 'try'.            | Jedi Master        |
| Knowledge speaks, but wisdom listens.        | Musician           |
| I would have made a good Pope.               | Former President   |
| To not risk is to know not sadness or joy    | Unknown Occupation |
| Character is much easier kept than recovered | Unknown Occupation |
+----------------------------------------------+--------------------+
5 rows in set (0.03 sec)

Looking at the preceding query, you see that we are selecting two columns that reside in two different tables. The first column is the quote column from the myquotes table (as indicated by myquotes.quote) and the second is the occupation column from the occupation table. Because we are selecting results from two different tables, both tables must be identified as part of the query. This is done in the next line of the query directly following the FROM portion of the SELECT statement where both the tables are listed. Finally, to dictate how these two independent tables are related, a WHERE clause must be provided. In this case, the clause myquotes.author = occupation.author is used, thus forming a query that relates the quotes column to the occupation column of another table by their common column author, as was shown in Figure 23.3.