by Curtiss   |   last updated March 1, 2008   |   programming · Software ·

During the second half of day 3, we began to delve into the information that will be presented on the MySQL Developer II examination. My notes from that session follow.

Day 3 – Second Half

Joins

• Combining information from multiple tables
• Three categories of joins
  • Inner join – identifies combinations of matching rows from two tables
    • SELECT * FROM m, w WHERE m.HHid = w.HHid; or SELECT * FROM m JOIN w ON m.HHid = w.HHid; will pull only matching records from each table:

    • +------+------+-------+------+ 
      | Name | HHid | Name  | HHid | 
      +------+------+-------+------+ 
      | Bob  |    1 | Carol |    1 | 
      | Ted  |    2 | Alice |    2 | 
      +------+------+-------+------+

    • SELECT * FROM m JOIN w USING (HHid); will combine the field “HHid” from both tables and move it all the way to the left of the resultset (will only work if the reference column is named the same in both tables)

    • +------+------+-------+ 
      | HHid | Name | Name  | 
      +------+------+-------+ 
      |    1 | Bob  | Carol | 
      |    2 | Ted  | Alice | 
      +------+------+-------+

    • SELECT * FROM m RIGHT JOIN w ON m.HHid = w.HHid WHERE m.Name IS NULL; will return all of the records from the right table (w) that do not have matches in the left table:

    • +------+------+-------+------+ 
      | Name | HHid | Name  | HHid | 
      +------+------+-------+------+ 
      | NULL | NULL | Sally |    4 | 
      | NULL | NULL | Ann   |    7 | 
      | NULL | NULL | Linda |    5 | 
      +------+------+-------+------+

  • Outer join – the same as inner join, but it also finds mismatches
    • SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid; will pull all of the information from the left table and any matching information from the right table. Any information that appears in the left table (m) that does not have a match in the right table (w) will be represented as NULL in the right side:

    • +------+------+-------+------+ 
      | Name | HHid | Name  | HHid | 
      +------+------+-------+------+ 
      | Ed   |    6 | NULL  | NULL | 
      | Ted  |    2 | Alice |    2 | 
      | Al   |    3 | NULL  | NULL | 
      | Bob  |    1 | Carol |    1 | 
      +------+------+-------+------+

    • SELECT * FROM m RIGHT JOIN w ON m.HHid=w.HHid; will pull all of the information from the right table (w) and any matching information from the left table (m). Any information that appears in the right table (w) that does not have a match in the left table (m) will be represented as NULL in the left side:

    • +------+------+-------+------+ 
      | Name | HHid | Name  | HHid | 
      +------+------+-------+------+ 
      | NULL | NULL | Sally |    4 | 
      | Bob  |    1 | Carol |    1 | 
      | Ted  |    2 | Alice |    2 | 
      | NULL | NULL | Ann   |    7 | 
      | NULL | NULL | Linda |    5 | 
      +------+------+-------+------+

  • Cross join – combines all of the records from one table with all the records of another table
  • • SELECT * FROM m, w; will match all records in m table with all records from w table:

    • +------+------+-------+------+ 
      | Name | HHid | Name  | HHid | 
      +------+------+-------+------+ 
      | Ed   |    6 | Sally |    4 | 
      | Ted  |    2 | Sally |    4 | 
      | Al   |    3 | Sally |    4 | 
      | Bob  |    1 | Sally |    4 | 
      | Ed   |    6 | Carol |    1 | 
      | Ted  |    2 | Carol |    1 | 
      | Al   |    3 | Carol |    1 | 
      | Bob  |    1 | Carol |    1 | 
      | Ed   |    6 | Alice |    2 | 
      | Ted  |    2 | Alice |    2 | 
      | Al   |    3 | Alice |    2 | 
      | Bob  |    1 | Alice |    2 | 
      | Ed   |    6 | Ann   |    7 | 
      | Ted  |    2 | Ann   |    7 | 
      | Al   |    3 | Ann   |    7 | 
      | Bob  |    1 | Ann   |    7 | 
      | Ed   |    6 | Linda |    5 | 
      | Ted  |    2 | Linda |    5 | 
      | Al   |    3 | Linda |    5 | 
      | Bob  |    1 | Linda |    5 | 
      +------+------+-------+------+

• Inner Join replaces comma separator (INNER JOIN is alias of JOIN, assuming the word “RIGHT” or “LEFT” does not precede the word “JOIN”)
• Useful clauses
  • FROM
  • ON
  • USING()
  • WHERE
    • SELECT COUNT(*), Name
      FROM CountryLanguage INNER JOIN Country
      ON CountryCode = Code
      GROUP BY Name
      HAVING COUNT(*) > 10;
• When retrieving information from multiple tables, you have to qualify any columns that might appear in multiple tables
• Self Joins
  • Joining a table to itself is ambiguous and can generate multiple errors
  • To properly perform a self-join, you need to alias your tablename:
    SELECT t1.IndepYear, t1.Name, t2.Name
    FROM Country AS t1 JOIN Country AS t2
    ON t1.IndepYear = t2=IndepYear AND t1.Name = ‘Qatar’;

Once you specify an alias for a table, you must use the alias to refer to that table from there on out within the statement

Multi-table updates and deletes

MySQL allows join syntax in UPDATE and DELETE

• UPDATE Operations
  • UPDATE t1, t2 SET t1.name=t2.name WHERE t1.id=t2.id;
• DELETE Operations
  • DELETE FROM t1 FROM t1, t2 WHERE t1.id=t2.id;
  • DELETE FROM t1 USING t1, t2 WHERE t1.id=t2.id;
• DELETE Operations that remove information from both tables
  • DELETE t1, t2 FROM t1, t2 WHERE t1.id=t2.id
  • DELETE FROM t1, t2 USING t1, t2 WHERE t1.id=t2.id;