Using JOINs

Joining data from two or more tables is how we unlock the power of a relational database such as MariaDB. There are three basic JOIN types: INNER, CROSS, and LEFT (or OUTER).

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Perform an INNER JOIN of the authors and emails tables to show us a list of authors and their corresponding e-mail addresses using the following command:

   SELECT author_canonical, email_address 
     FROM authors INNER JOIN emails 
       ON authors.author_id = emails.author_id;
   

3. Perform a LEFT JOIN of the emails and authors tables to show us a list of authors and their corresponding e-mail addresses using the following command:

   SELECT author_canonical, email_address 
     FROM emails LEFT JOIN authors 
       ON authors.author_id = emails.author_id;
   

4. Perform...