List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 29 1999 4:16pm
Subject:problem with natural joins (version 3.22.21)
View as plain text  
Hi!

>>>>> "Egbert" == Egbert Engel <Egbert.Engel@stripped>
> writes:

Egbert> Hi,
Egbert> I have a problem when joining two tables (table1, table2).
Egbert> Table 1's primary key column also exists in table2 as "foreign key" column
Egbert> (I know that there actually are no foreign keys in MySQL).

I assume you also know that foreign keys has nothing to do with
joining tables in a SELECT statement!

Egbert> I use the following join syntax:
>> select * from table1 natural left join table2;
Egbert> which returns the complete result of a regular join, i.e. without using
Egbert> the constraint of two columns with same name.

Egbert> The following, however:
>> select * from table2 natural left join table1;
Egbert> returns the correct result .

Egbert> I checked the other possibilities (LEFT JOIN ON..., LEFT JOIN
Egbert> USING...), too, with the same result. CROSS JOIN... works, of course, but
Egbert> I don't want to use it.

Egbert> I've never had this problem before and really NO idea what is wrong (or
Egbert> maybe I'm too stupid!). Would be very nice If somebody gave me a
Egbert> hint.

Which MySQL version are you using?
Could you post a full example that shows this?

Here is a test that shows that it works (at least for me:)

mysql> CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,'a',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (2,'b',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (3,'c',2);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (1,'x');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (2,'y');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (3,'z');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+-----+
| id2 |
+-----+
|   3 |
+-----+
1 row in set (0.03 sec)

mysql> SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;

+-----+------+------+------+------+
| id2 | dat2 | id1  | dat1 | id2  |
+-----+------+------+------+------+
|   1 | x    |    1 | a    |    1 |
|   1 | x    |    2 | b    |    1 |
|   2 | y    |    3 | c    |    2 |
|   3 | z    | NULL | NULL | NULL |
+-----+------+------+------+------+

Regards,
Monty
Thread
problem with natural joins (version 3.22.21)Egbert Engel26 Aug
  • problem with natural joins (version 3.22.21)Michael Widenius29 Aug