One way MS Access Differs from MySQL

Posted: March 3rd, 2005 | Author: telcor | Filed under: SQL | No Comments »

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.childFROM  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.childFROM  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.