MySQL Joins in More Depth

Joins to the left of me, Joins to the Right of me....

Types of Joins

• SQL Join Types
• MySQL.com Join Types
• Inner Joins (can just be JOIN in MySQL) are basically the default
• LEFT JOIN (based on the FIRST table after the FROM statement)
• RIGHT JOIN (based on the LAST table after the FROM statement)
• FULL JOIN (based on a LEFT OUTER JOIN union RIGHT OUTER JOIN for two queries)

• UNION normally (ie not MySQL) requires that items in a column have the same or similar data types

  select author_id, whereborn from authors;
  
  select title,yearpub from books;
  
  /*
  Now to show what MySQL allows, which is just meaningless:
  */
  
  select author_id, whereborn from authors
  UNION
  select title,yearpub from books;
  

Examples

Note: You will need this books db file run before you can do this on your VM 1. (INNER) JOIN

    SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
    FROM books b JOIN books_authors ba ON (b.book_id= ba.book_id)
    JOIN authors a ON (ba.author_id=a.author_id);

1. Left Join

   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
   FROM books b LEFT JOIN books_authors ba ON (b.book_id= ba.book_id)
   LEFT JOIN authors a ON (ba.author_id=a.author_id);
   
   /*
   which shows two books that have no authors
   
   to simplify and JUST show the books with null authors
   
   Important NOTE:  in your where clause you can NOT do  `= null` as that is 
   impossible with how the database treats empty values, instead you can use 
   `IS NULL`  and `IS NOT NULL`
   */
   
   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
   FROM books b LEFT JOIN books_authors ba ON (b.book_id= ba.book_id)
   LEFT JOIN authors a ON (ba.author_id=a.author_id)
   Where a.last_name IS NULL or a.first_name IS NULL;
   

2. RIGHT (outer) JOIN

   /*
   Add a few "orphaned" authors:
   */
   
   INSERT into authors (last_name,first_name) values
   ('Wiseau','Tommy'),
   ('Kahn','Genghis'),
   ('McCaffery','Anne');
   
   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
   FROM books b RIGHT JOIN books_authors ba ON (b.book_id= ba.book_id)
   RIGHT JOIN authors a ON (ba.author_id=a.author_id);
   
   /*
   Again, to show just the Null values
   */
   
   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
   FROM books b RIGHT JOIN books_authors ba ON (b.book_id= ba.book_id)
   RIGHT JOIN authors a ON (ba.author_id=a.author_id)
   Where b.title IS NULL;
   

3. FULL JOIN (have to fake it with a UNION)

   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order 
   FROM books b LEFT JOIN books_authors ba ON (b.book_id= ba.book_id) LEFT JOIN authors a ON (ba.author_id=a.author_id) 
   Where a.last_name IS NULL or a.first_name IS NULL
   UNION
   SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
   FROM books b RIGHT JOIN books_authors ba ON (b.book_id= ba.book_id)
   RIGHT JOIN authors a ON (ba.author_id=a.author_id)
   Where b.title IS NULL;
   

Page last updated on 20211013