Even though querying MySQL is quite fast, especially if it is in the same server as our PHP application, we should try to reduce the number of queries that we will execute to improve the performance of our application. So far, we have queried data from just one table, but this is rarely the case. Imagine that you want to retrieve information about borrowed books: the table contains only IDs and dates, so if you query it, you will not get very meaningful data, right? One approach would be to query the data in borrowed_books, and based on the returning IDs, query the book and customer tables by filtering by the IDs we are interested in. However, this approach consists of at least three queries to MySQL and a lot of work with arrays in PHP. It seems as though there should be a better option!

In SQL, you can execute join queries. A join query is a query that joins two or more tables through a common field and, thus, allows you to retrieve data from these tables, reducing the amount of queries needed. Of course, the performance of a join query is not as good as the performance of a normal query, but if you have the correct keys and relationships defined, this option is way better than querying separately.

In order to join tables, you need to link them using a common field. Foreign keys are very useful in this matter as you know that both the fields are the same. Let's take a look at how we would query for all the important info related to the borrowed books:

mysql> SELECT CONCAT(c.firstname, ' ', c.surname) AS name,
    ->     b.title,
    ->     b.author,
    ->     DATE_FORMAT(bb.start, '%d-%m-%y') AS start,
    ->     DATE_FORMAT(bb.end, '%d-%m-%y') AS end
    -> FROM borrowed_books bb
    ->     LEFT JOIN customer c ON bb.customer_id = c.id
    ->     LEFT JOIN book b ON b.id = bb.book_id
    -> WHERE bb.start >= "2015-01-01";
+------------+---------+---------------+----------+----------+
| name       | title   | author        | start    | end      |
+------------+---------+---------------+----------+----------+
| Han Solo   | Dracula | Bram Stoker   | 10-01-15 | 13-01-15 |
| James Kirk | Dracula | Bram Stoker   | 01-02-15 | 10-02-15 |
| James Kirk | 1984    | George Orwell | 12-03-15 | NULL     |
+------------+---------+---------------+----------+----------+
3 rows in set (0.00 sec)

There are several new concepts introduced in this last query. Especially with joining queries, as we joined the fields of different tables, it might occur that two tables have the same field name, and MySQL needs us to differentiate them. The way we will differentiate two fields of two different tables is by prepending the name of the table. Imagine that we want to differentiate the ID of a customer from the ID of the book; we should use them as customer.id and book.id. However, writing the name of the table each time would make our queries endless.

MySQL has the ability to add an alias to a table by just writing next to the table's real name, as we did in borrowed_books (bb), customer (c) or book (b). Once you add an alias, you can use it to reference this table, allowing us to write things such as bb.customer_id instead of borrowed_books.customer_id. It is also good practice to write the table of the field even if the field is not duplicated anywhere else as joining tables makes it a bit confusing to know where each field comes from.

When joining tables, you need to write them in the FROM clause using LEFT JOIN, followed by the name of the table, an optional alias, and the fields that connect both tables. There are different joining types, but let's focus on the most useful for our purposes. Left joins take each row from the first table—the one on the left-hand side of the definition—and search for the equivalent field in the right-hand side table. Once it finds it, it will concatenate both rows as if they were one. For example, when joining borrowed_books with customer for each borrowed_books row, MySQL will search for an ID in customer that matches the current customer_id, and then it will add all the information of this row in our current row in borrowed_books as if they were only one big table. As customer_id is a foreign key, we are certain that there will always be a customer to match.

You can join several tables, and MySQL will just resolve them from left to right; that is, it will first join the two first tables as one, then try to join this resulting one with the third table, and so on. This is, in fact, what we did in our example: we first joined borrowed_books with customer and then joined these two with book.

As you can note, there are also aliases for fields. Sometimes, we do more than just getting a field; an example was when we got how many rows a query matched with COUNT(*). However, the title of the column when retrieving this information was also COUNT(*), which is not always useful. At other times, we used two tables with colliding field names, and it makes everything confusing. When this happens, just add an alias to the field in the same way we did with table names; AS is optional, but it helps to understand what you are doing.

Let's move now to the usage of dates in this query. On one hand, we will use DATE_FORMAT for the first time. It accepts the date/time/datetime value and the string with the format. In this case, we used %d-%m-%y, which means day-month-year, but we could use %h-%i-%s to specify hours-minutes-seconds or any other combination.

Note also how we compared dates in the WHERE clause. Given two dates or time values of the same type, you can use the comparison operators as if they were numbers. In this case, we will do bb.start >= "2015-01-01", which will give us the borrowed books from January 1, 2015, onward.

The final thing to note about this complex query is the use of the CONCAT function. Instead of returning two fields, one for the name and one for the surname, we want to get the full name. To do this, we will concatenate the fields using this function, sending as many strings as we want as arguments of the function and getting back the concatenated string. As you can see, you can send both fields and strings enclosed by single quotes.

Well, if you fully understood this query, you should feel satisfied with yourself; this was the most complex query we will see in this chapter. We hope you can get a sense of how powerful a database system can be and that from now on, you will try to process the data as much as you can on the database side instead of the PHP side. If you set the correct indexes, it will perform better.