Nuno Pereira <nuno.pereira@stripped> wrote on 08/26/2005 02:30:08 PM:
> SGreen@stripped wrote:
> > Nuno Pereira <nuno.pereira@stripped> 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
