The two main types of joins are inner and outer joins, with subtypes within each type.

Often an alias will be used as a shorthand way of referencing the same table multiple times within the same query.

When selecting from multiple tables, you must use the dot syntax if the tables named in the query have columns with the same name. (In relational databases, it is common for the primary key in one table to have the same name as a foreign key in a related table.) If you are not explicit when referencing the columns, an error that reports an ambiguous column name will occur.

The left outer join is the most common, and in a left outer join, the first table named will be the one that has all of its records returned. It is important because if you are interested in finding the status or some condition related to all of the records in a table, including records that have no matches in the remainder of the join, that is the table that you want to list first. The example used in the book to illustrate this is that of finding the number of messages in each of the forums that exist in the forums table. In an inner join, if there were no messages in a particular forum, there would be nothing reported. In a left outer join, with the forum table listed first, the forum would be listed with a NULL value for messages if there were no messages that matched.

A self-join is a join of a table with itself. The trick with self-joins is to treat the two references to the same table as if they were single references to two different tables. The way to do that is to assign a different alias to each table reference.

The example that he gives in the book with the forum messages table, for finding messages that are replies to a parent message, is as follows (with a slight modification):

SELECT m1.message_id,m1.subject, m2.subject AS Reply FROM messages AS m1 LEFT JOIN messages AS m2 ON m1.message_id=m2.parent_id where m1.parent_id=0

This returns a result set like this:
+------------+-------------------------------+------------------------+
| message_id | subject                       | Reply                  |
+------------+-------------------------------+------------------------+
|          1 | Question about normalization. | NULL                   |
|          2 | Database Design               | Database Design        |
|          4 | Database Design               | NULL                   |
|          5 | PHP Errors                    | PHP Errors             |
|          9 | Chicago Bulls                 | Chicago Bulls          |
|         11 | CSS Resources                 | CSS Resources          |
|         13 | HTML vs. XHTML                | HTML vs. XHTML         |
|         15 | Why?                          | Why? Why? Why?         |
|         16 | Dynamic HTML using PHP        | Dynamic HTML using PHP |
+------------+-------------------------------+------------------------+
9 rows in set (0.00 sec)

The messages that have no replies have NULL in the Reply field. The state of the messages table at the time of this query was:

mysql> select message_id,parent_id,forum_id,subject from messages;
+------------+-----------+----------+-----------------------------------------+
| message_id | parent_id | forum_id | subject                                 |
+------------+-----------+----------+-----------------------------------------+
|          1 |         0 |        1 | Question about normalization.           |
|          2 |         0 |        1 | Database Design                         |
|          3 |         2 |        1 | Database Design                         |
|          4 |         0 |        1 | Database Design                         |
|          5 |         0 |        2 | PHP Errors                              |
|          6 |         5 |        2 | PHP Errors                              |
|          7 |         6 |        2 | PHP Errors                              |
|          8 |         7 |        2 | PHP Errors                              |
|          9 |         0 |        3 | Chicago Bulls                           |
|         10 |         9 |        3 | Chicago Bulls                           |
|         11 |         0 |        5 | CSS Resources                           |
|         12 |        11 |        5 | CSS Resources                           |
|         13 |         0 |        4 | HTML vs. XHTML                          |
|         14 |        13 |        4 | HTML vs. XHTML                          |
|         15 |         0 |        6 | Why?                                    |
|         16 |         0 |        2 | Dynamic HTML using PHP                  |
|         17 |        16 |        2 | Dynamic HTML using PHP                  |
|         18 |        17 |        2 | Dynamic HTML using PHP, still not clear |
|         19 |        18 |        2 | Dynamic HTML using PHP, clearer?        |
|         20 |        15 |        6 | Why? Why? Why?                          |
|         21 |        20 |        6 | Because                                 |
+------------+-----------+----------+-----------------------------------------+
21 rows in set (0.00 sec)

To pick a couple examples to explain the result of the join, the message with the message_id=1 has a parent_id of 0, so it is a root message. There are no other messages in the table that have a parent_id=1, therefore that message has no replies, it is listed with NULL in the "Reply" column of the result.
The message with message_id=2 has a parent_id of 0, so it is a root message. Scanning the table of messages, it can be seen that there is another message, with message_id=3, that has message_id=2 as its parent. So the reply column shows the subject line of message_id 3 as the Reply.

A slight modification of that query, showing the parent message_id as "P_id", and the child or reply's message_id as "R_id" might provide some additional clarification of the relationships:

mysql> SELECT m1.message_id AS P_id, m1.subject, m2.subject AS Reply, m2.message_id AS R_id FROM messages AS m1 LEFT JOIN messages AS m2 ON m1.message_id=m2.parent_id WHERE m1.parent_id=0;
+------+-------------------------------+------------------------+------+
| P_id | subject                       | Reply                  | R_id |
+------+-------------------------------+------------------------+------+
|    1 | Question about normalization. | NULL                   | NULL |
|    2 | Database Design               | Database Design        |    3 |
|    4 | Database Design               | NULL                   | NULL |
|    5 | PHP Errors                    | PHP Errors             |    6 |
|    9 | Chicago Bulls                 | Chicago Bulls          |   10 |
|   11 | CSS Resources                 | CSS Resources          |   12 |
|   13 | HTML vs. XHTML                | HTML vs. XHTML         |   14 |
|   15 | Why?                          | Why? Why? Why?         |   20 |
|   16 | Dynamic HTML using PHP        | Dynamic HTML using PHP |   17 |
+------+-------------------------------+------------------------+------+
9 rows in set (0.00 sec)

Examining the first row that is returned in the modified query, the P_id is "1", that is a message that has no "children", so the Reply is NULL, the R_id is NULL. The second row in the table shows that it is a parent message with the id of 2, and there is another message with a parent id of 2, that being message 3. So the second line in the result set shows that the "R_id" for parent 2 is message 3. (This could go on ad infinitum so it might be best to stop here!)

The aggregate functions or grouping functions return a value based on a single column over a set of rows. The following table lists the grouping functions.

SQL Grouping Functions

Function Returns

AVG( ) The average of the values in a column.

COUNT( ) The number of values in a column.

GROUP_CONCAT( ) The concatenation of a column's values.

MAX( ) The largest value in a column.

MIN( ) The smallest value in a column.

SUM( ) The sum of all the values in a column.

The DISTINCT keyword make the aggregation only apply to distinct (or unique) values. The example that is used in the book is that the query SELECT COUNT(customer_id) FROM accounts will return the number of accounts, regardless of who owns the accounts. The query SELECT COUNT(DISTINCT customer_id) FROM accounts will return the number of customers that have accounts.

A FULLTEXT search requires a FULLTEXT index. This is only supported on the MyISAM table.

Instead of presenting the results of the query in a tabular format, it will display the results as a vertical list. This can make it easier to examine the results of queries that return voluminous information.

An IN BOOLEAN MODE FULLTEXT search is more sophisticated than a normal FULLTEXT search in that it has a number of operators that tweak how each keyword is treated. You can use the '+' operator to indicate that a word must be present in a search. The '-' operator can indicate words that must NOT be present. The '>' operator will increase a word's importance, whereas the '<' operator will decrease the importance of a word, affecting the rankings of the matching records. The asterisk is used as a wildcard character

, the tilde '~' is used to reduce the ranking if a search term is present. Several other differences compared to a normal FULLTEXT search:

• If a keyword is not preceded by an operator, the word is optional but a match will be ranked higher if it is present
• Results will be returned even if more than 50% of the records match the search
• The results are not automatically sorted by relevance

Because the results are not sorted by relevance, that is an extra step that you need to add to the query.

An example query and returned results, sorted by relevance, is:

mysql> SELECT subject, body, MATCH(body, subject) AGAINST('*HTML >XHTML' IN BOOLEAN MODE) AS R FROM messages WHERE MATCH(body, subject) AGAINST('*HTML >XHTML' IN BOOLEAN MODE) ORDER BY R DESC\G
*************************** 1. row ***************************
subject: HTML vs. XHTML
   body: What are the differences between HTML and XHTML?
      R: 2.5
*************************** 2. row ***************************
subject: HTML vs. XHTML
   body: XHTML is a cross between HTML and XML. The differences are largely syntactic. Blah, blah, blah...
      R: 2.5
*************************** 3. row ***************************
subject: CSS Resources
   body: Read Elizabeth Castro's excellent book on (X)HTML and CSS. Or search Google on "CSS".
      R: 1
*************************** 4. row ***************************
subject: Dynamic HTML using PHP
   body: Can I use PHP to dynamically generate HTML on the fly? Thanks...
      R: 1
*************************** 5. row ***************************
subject: Dynamic HTML using PHP
   body: You most certainly can.
      R: 1
*************************** 6. row ***************************
subject: Dynamic HTML using PHP, still not clear
   body: Um, how?
      R: 1
*************************** 7. row ***************************
subject: Dynamic HTML using PHP, clearer?
   body: I think what Larry is trying to say is that you should buy and read his book.
      R: 1
7 rows in set (0.00 sec)

mysql>

Any record that has the exact phrase 'XHTML' is ranked higher than others, but 'XHTML' is not required in the match. (This is another answer that could go on and on, suffice it to say that this is a much more flexible and powerful search technique.)

One command that helps to maintain the table and remove unnecessary overhead is the OPTIMIZE command. This should be used after a table has been changed with the ALTER command, or after many DELETE queries have been run.

A second command is the ANALYZE command, which updates the indexes on the table.

You can use the EXPLAIN query command, which gives an indication of how MySQL will run a query. This gives details on the way a query will be conducted, perhaps indicating where an index should be added, where a table should be modified, etc.

Transactions are only supported by the InnoDB storage engine. The forum database uses the MyISAM engine for the messages table, therefore it cannot have transaction support.

A transaction is started with a START TRANSACTION; command. Queries that would modify the database are performed (primarily UPDATE queries - because queries that create, alter, truncate or delete tables cannot be undone.) While a transaction is in progress and not terminated, the ROLLBACK; command will return the database to its initial condition. The effects of the current transaction are made permanent with either the COMMIT command, or by executing one of the aforementioned actions such as CREATE TABLE, etc. COMMIT; is the normal way.

The AES_ENCRYPT() function requires a binary data type, for example VARBINARY or BLOB.

The 'salt' argument is a string that helps to randomize the encryption. The exact same salt value is needed for both the encryption and decryption processes, so it must also be stored somewhere (most likely in the database along with the encrypted value.) One final consideration is that each piece of stored data should use a unique salt, and the longer the salt value, the better.