Adventures As Me

One way MS Access Differs from MySQL

Written 03 Mar 2005

Previously, I shot some holes in an article touting the advantages of MySQL over MS Access. Today I want to show one significant advantage MySQL does have. The advantage pertains to the implementation of JOIN operators. First, pointers to documentation for each product:

The first thing that becomes apparent while reading the documentation, MS Access only allows multiple JOINs, via nesting. Further, one can only nest certain JOIN types. Note this:

Although a LEFT JOIN or RIGHT JOIN operation can be nested inside an INNER JOIN, the converse is not true. An INNER JOIN operation cannot be nested inside a LEFT JOIN or RIGHT JOIN.

The nesting impresses limits upon the JOINs. For example, suppose you have four tables to JOIN: table1, table2, table3, table4. MySQL allows the following syntax:

SELECT
  name,
  company,
  table2.ssn,
  table3.phone,
  table4.child
FROM
  table1
  LEFT JOIN table2
  ON table1.id=table2.id
  LEFT JOIN table3
  ON table1.id=table3.id
  LEFT JOIN table4
  ON table1.id=table4.id

MS Access does not allow such. You can only have one LEFT or RIGHt JOIN per SELECT statement. To have more, you must use an INNER JOIN first, followed by a series of nested LEFT or RIGHT JOINs. The tables JOINed in the nesting are not JOINed to the first (the primary) table, but JOINed to the table nested against. That sounds strange, so let this suffice as an example:

SELECT
  name,
  company,
  table2.ssn,
  table3.phone,
  table4.child
FROM
  table1
  INNER JOIN (table2
    INNER JOIN (table3
      INNER JOIN table4
      ON table3.id=table4.id)
    ON table2.id=table3.id)
  ON table1.id=table2.id

Instead of tables 2, 3 and 4 being directly related to table1, MS Access forces a cascading type of relationship:

table1
  -> table2
    -> table3
      -> table4

Now it must be admitted that the provided example is simplistic, and is easily rewritten using NATURAL JOINs in WHERE clauses. That is often the consequence of providing easily understood examples. The point still stands though, MS Access limits the way one JOINs tables, restristing to a cascading JOIN.

Related Posts