OUTER portion of LEFT JOIN defunct?
am 12.08.2002 14:36:19 von Nic IvyWhile running MySQL 4.0.2-alpha under Win32, the OUTER portion of a LEFT
JOIN causes me trouble. If two columns of the same name, but from
different tables, are SELECTed, the left column appears as NULL when the
right column is NULL. Modifying the command to use RIGHT JOIN, the OUTER
portion of the results appear as expected.
Nic Ivy
How-To-Repeat:
-----------------------------------------------------
CREATE TABLE left_table (
id varchar(255) NOT NULL default '',
data varchar(255) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO left_table VALUES ('1', 'a');
INSERT INTO left_table VALUES ('2', 'b');
INSERT INTO left_table VALUES ('3', 'c');
CREATE TABLE right_table (
id varchar(255) NOT NULL default '',
value varchar(255) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO right_table VALUES ('2', 'baby');
INSERT INTO right_table VALUES ('3', 'car');
INSERT INTO right_table VALUES ('4', 'dog');
# This command appears to give incorrect results.
# Shouldn't there be a row of ('1', NULL)?
SELECT left_table.id,right_table.id FROM left_table LEFT JOIN right_table
ON left_table.id=right_table.id
# This similar command gives the expected results.
# There is a row of (NULL, '4').
SELECT left_table.id,right_table.id FROM left_table RIGHT JOIN
right_table ON left_table.id=right_table.id
# Incidentally, this command gives the same strange results as the
# first LEFT JOIN command.
SELECT left_table.id,right_table.id FROM right_table RIGHT JOIN left_table
ON right_table.id=left_table.id
-----------------------------------------------------
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12349@lists.mysql.com
To unsubscribe, e-mail