One way MS Access Differs from MySQL

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:

• MS Access JET JOIN Syntax: INNER JOIN, LEFT JOIN, RIGHT JOIN
• MySQL JOIN Syntax

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.

Access vs MySQL - Refuting article errors

While ordinarily, I’m no defender of Microsoft
applications, while searching for a feature comparison between MySQL
and MS Access I found the article
MySQL versus Access to
be very wrong on several points. If you do a feature comparision, at least
check that your data is correct. Now, I could not find a published date for
the article, thus it is possible the author was comparing a very old version
of Access. Let us examine some of his points.

• You want your data to be deployed with more flexibility.
  Here, the author presents MySQL being better because it can be accessed via
  numerous ways. The same is true of Access. It can be accessed via ODBC connections,
  ADODB calls, graphical client and integrated with a web browser using any of a
  number of server side languages.
• You are not the only person who control the data. Since
  at least version 97, Access has supported multiple users. The ease of doing
  this is at least as complicated as MySQL’s. Check the help documentation how
  to do this.
• You want your data secured and only accessed by authorized people.
  His claims here are strange, and show a lack of understanding of some security
  basics. Access can be made as secure as MySQL. MySQL can be made as insecure
  as he claims Access is. It comes down to quality administration and following
  basic security guidelines.

This reduces his valid points to three. Not necessarily enough to make a true
comparison between the two. It rather strangles his statement After a
thorough understanding of the pros and cons of Access and MySQL since his
article doesn’t even come near to fully exploring pros and cons for either.

The sad part is, all this can be found by reading the manual. This
indicates to me the article writer has no experience with Access and
thus is unqualified to write an article comparing it to MySQL.