OUTER JOIN and FULL OUTER JOIN simplest implementations?

Question

Suspect but not sure: Is a simple list of tables in FROM-clause is a full join by definition?

SELECT * 
FROM table1, table2 

and is the case where we are joining tables on condition of not equal parameters is an implementation of the full outer join or not? E.g.:

SELECT * 
FROM table1
JOIN table2
ON table1.id <> table2.id 

A little bit more details, i found so far:

"CROSS JOIN returns the Cartesian product" http://en.wikipedia.org/wiki/Join_(SQL)

The list of tables to join may be specified in the following ways: "Table1, Table2, Table3,... This is the simplest form. The tables are joined in the manner that MySQL deems most efficient. This method can also be written as Table1 JOIN Table2 JOIN Table3,... The CROSS keyword can also be used, but it has no effect (e.g., Table1 CROSS JOIN Table2) Only rows that match the conditions for both columns are included in the joined table." MySQL Pocket Reference, Second Edition by George Reese

So, the first one really seems to be a CROSS JOIN of the tables, or a Cartesian product.

Answer 1

MySQL still lacks support for FULL JOIN. One way to emulate it

SELECT a.id id0, t1.id id1, t2.id id2
  FROM
(
  SELECT id
    FROM table1
  UNION
  SELECT id
    FROM table2
) a LEFT JOIN table1 t1
   ON a.id = t1.id LEFT JOIN table2 t2
   ON a.id = t2.id;

Here is SQLFiddle demo. This demo also includes both your queries. See the difference in result sets.

Answer 2

let's examine your second query:

As an example we are using the following tables:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Important: Not all primary keys of table t1 exist in table t2 and vice versa!

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1,'Tim'),(2,'Marta');
INSERT INTO `t2` VALUES (1,'Tim'),(3,'Katarina');

Now we run your query:

SELECT * 
FROM t1
JOIN t2
ON t1.id <> t2.id 

The result set is:

id    name   id   name
===========================
2     Marta  1    Tim
1     Tim    3    Katarina
2     Marta  3    Katarina

What happened? You selected all combinations that do not have a match!

Let's have a look at the definition of a FULL OUTER JOIN:

A FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the table.

Ok, we also need the definition for a left and right outer join:

A LEFT OUTER JOIN returns all the records from the left table irrespective of the match with the right table.

A RIGHT OUTER JOIN returns all the records from the right table irrespective of the match with the left table.

Let's do this manually:

The LEFT OUTER JOIN returns Tim and Marta from the left table, with Tim as only match:

id    name   id   name
===========================
1     Tim    1    Tim
2     Marta  NULL NULL

The RIGHT OUTER JOIN returns tim and katarina from the right table, with Tim as only match:

id    name   id   name
===========================
1     Tim    1    Tim
NULL  NULL   3    Katarina

If we combine these two to an FULL OUTER JOIN we get:

id    name   id   name
===========================
1     Tim    1    Tim
2     Marta  NULL NULL
NULL  NULL   3    Katarina

This is the correct result set. In MySQL this can be done by an UNION of the left and right outer join:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;