LEFT JOIN get more than FULL JOIN?

Hi there,

I have two tables (probe and dbxref):

[probe]
id value

[dbxref]
probe_id db_id

I imagine that a query using FULL JOIN should always get lines no less
than a query using LEFT JOIN , but the result frustrated me:

mysql> SELECT count(id) FROM probe FULL JOIN dbxref ON id=probe_id;
+-----------+
| count(id) |
+-----------+
| 96552 |
+-----------+
1 row in set (0.27 sec)

mysql>
mysql> SELECT count(id) FROM probe LEFT JOIN dbxref ON id=probe_id;
+-----------+
| count(id) |
+-----------+
| 101601 |
+-----------+
1 row in set (1.12 sec)

Anybody can explain why this happened?

Thanks very much,

York

Hi there,

You might find this interesting: http://bugs.mysql.com/bug.php?id=15457

It says that "full join" or "full outer join" is not (yet officially)
implemented in MySQL, so you are using an undocumented feature (or a
buggy implementation of full outer join)

If you google on "MySQL FULL JOIN", you'll find some workarounds.

Have fun.

Peet