Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/26/2005 02:30:08 PM: > [EMAIL PROTECTED] wrote: > > Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/26/2005 02:08:50 PM: > > > > > >>Hi list, > >> > >>I have a query like this (the original is very big and names of > >>columns/tables are in portuguese): > >> > >>SELECT * FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id) > >> > >>For a schema like this: > >> > >>CREATE TABLE tbl2 ( > >>id INT NOT NULL auto_increment PRIMARY KEY, > >>data_col2 int NOT NULL > >>) ENGINE=InnoDB; > >> > >>CREATE TABLE tbl1 ( > >>id INT NOT NULL auto_increment PRIMARY KEY, > >>id_tbl2 int NULL, > >>data_col1 int NOT NULL, > >> > >>INDEX (id_tbl2), > >>FOREIGN KEY (id_tbl2) > >> REFERENCES tbl2(id) ON UPDATE CASCADE > >>) ENGINE=InnoDB; > >> > >>but i have NULL values in t.id_tbl2. > >> > >>Here is some data: > >> > >>mysql> INSERT INTO tbl2 VALUES (NULL, 11),(NULL, 12); > >>Query OK, 2 rows affected (0.01 sec) > >>Records: 2 Duplicates: 0 Warnings: 0 > >> > >>mysql> SELECT * FROM tbl2; > >>+----+-----------+ > >>| id | data_col2 | > >>+----+-----------+ > >>| 5 | 11 | > >>| 6 | 12 | > >>+----+-----------+ > >>2 rows in set (0.00 sec) > >> > >>mysql> INSERT INTO tbl1 VALUES (NULL, 1, 123),(NULL, 2, 456), (NULL, > >>NULL, 789); > >>Query OK, 3 rows affected (0.00 sec) > >>Records: 3 Duplicates: 0 Warnings: 0 > >> > >>mysql> SELECT * FROM tbl1; > >>+----+---------+-----------+ > >>| id | id_tbl2 | data_col1 | > >>+----+---------+-----------+ > >>| 1 | 1 | 123 | > >>| 2 | 2 | 456 | > >>| 3 | NULL | 789 | > >>+----+---------+-----------+ > >>3 rows in set (0.00 sec) > >> > >> > >>The above query gives: > >> > >>mysql> SELECT t.id, t.data_col1, u.data_col2 FROM tbl1 t JOIN tbl2 u ON > >>(t.id_tbl2=u.id); > >>+----+-----------+-----------+ > >>| id | data_col1 | data_col2 | > >>+----+-----------+-----------+ > >>| 1 | 123 | 11 | > >>| 2 | 456 | 12 | > >>+----+-----------+-----------+ > >>2 rows in set (0.01 sec) > >>But should be > >> > >>+----+-----------+-----------+ > >>| id | data_col1 | data_col2 | > >>+----+-----------+-----------+ > >>| 1 | 123 | 11 | > >>| 2 | 456 | 12 | > >>| 3 | 789 | NULL | > >>+----+-----------+-----------+ > >> > >>Any sugestion? > >> > >>-- > >>Nuno Pereira > >> > > > > Change your INNER JOIN to a LEFT JOIN and all will be as you wanted. > > > > SELECT * FROM tbl1 t LEFT JOIN tbl2 u ON (t.id_tbl2=u.id) > > > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > This solved the problem to this test (bad) case, but not to real > situation. Monday I will place the full test. > > -- > Nuno Pereira >
If you want to perform JOINs across columns that can contain NULL values, you have to use the special "null-safe" equality comparitor: <=> Let's say that both columns across the join could have NULL as a valid value, you would match the NULL values to each other like this: SELECT * FROM tbl1 t INNER JOIN tbl2 u ON (t.id_tbl2<=>u.id) Otherwize, they would not match even though neither column in either table had a value in it. That's just how NULL works! Please read for more information: http://dev.mysql.com/doc/mysql/en/comparison-operators.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
