6.8.1. MySQL Features

MySQL goes well beyond mSQL's support for SQL by supporting functions and a limited concept of outer joins. Functions in SQL are similar to functions in other programming languages like C and Perl. The function takes zero or more arguments and returns some value. For example, the function SQRT(16) returns 4. Within a MySQL SELECT statement, functions may be used in either of two places:

As a value to be retrieved

This form involves a function in the place of a column in the list of columns to be retrieved. The return value of the function, evaluated for each selected row, is part of the returned result set as if it were a column in the database. For example:

# Select the name of each event as well as the date of the event
# formatted in a human-readable form for all events more
# recent than the given time. The FROM_UnixTIME() function
# transforms a standard Unix time value into a human
# readable form.
SELECT name, FROM_UnixTIME(date) 
FROM events
WHERE time > 90534323

# Select the title of a paper, the full text of the paper,
# and the length (in bytes) of the full text for all 
# papers authored by Stacie Sheldon.
# The LENGTH() function returns the character length of
# a given string.
SELECT title, text, LENGTH(text)
FROM papers
WHERE author = 'Stacie Sheldon'

As part of a WHERE clause

This form involves a function in the place of a constant when evaluating a WHERE clause. The value of the function is used for comparison for each row of the table. For example:

# Randomly select the name of an entry from a pool of 35
# entries. The RAND() function generates a random number
# between 0 and 1 (multiplied by 34 to make it between 0 
# and 34 and incremented by 1 to make it between 1 and
# 35). The ROUND() function returns the given number 
# rounded to the nearest integer, resulting in a whole
# number between 1 and 35, which should match one of
# the ID numbers in the table.
SELECT name 
FROM entries 
WHERE id = ROUND( (RAND()*34) + 1 )

# You may use functions in both the value list and the
# WHERE clause. This example selects the name and date
# of each event less than a day old. The UNIX_TIMESTAMP()
# function, with no arguments, returns the current time 
# in Unix format.
SELECT name, FROM_UnixTIME(date)
FROM events
WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24) )

# You may also use the value of a table field within
# a function. This example returns the name of anyone 
# who used their name as their password. The ENCRYPT()
# function returns a Unix password-style encryption
# of the given string using the supplied 2-character salt.
# The LEFT() function returns the left-most n characters 
# of the given string.
SELECT name
FROM people
WHERE password = ENCRYPT(name, LEFT(name, 2))

Finally, MySQL supports a more powerful joining than the simple inner joins we have used so far. Specifically, MySQL supports something called a left outer join (also known as simply outer join). This type of join is similar to an inner join, except that it includes data in the first column named that does not match any in the second column. If you remember our author and book tables from earlier in the chapter, you will remember that our join would not list any authors who did not have a book in our database. It is common that you may want to show entries from one table that have no corresponding data in the table to which you are joining. That is where an outer join comes into play:

SELECT book.title, author.name 
FROM author
LEFT JOIN book ON book.author = author.id

+----------------+----------------+
| book.title     | author.name    |
+----------------+----------------+
| The Green Mile | Stephen King   |
| Guards, Guards!| Terry Pratchett|
| Imzadi         | Peter David    |
| Gold           | Isaac Asimov   |
| Howling Mad    | Peter David    |
| NULL           | Neil Gaiman    |
+----------------+----------------+

MySQL takes this concept one step further through the use of a natural outer join. A natural outer join will combine the rows from two tables where the two tables have identical column names with identical types and the values in those columns are identical:

SELECT my_prod.name 
FROM my_prod
NATURAL LEFT JOIN their_prod

6.8.2. mSQL Features