List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 26 2005 6:38pm
Subject:Re: SELECT ... JOIN with NULL values
View as plain text  
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
Thread
SELECT ... JOIN with NULL valuesNuno Pereira26 Aug
  • Re: SELECT ... JOIN with NULL valuesSGreen26 Aug
    • Re: SELECT ... JOIN with NULL valuesNuno Pereira26 Aug
      • Re: SELECT ... JOIN with NULL valuesNuno Pereira26 Aug
      • Re: SELECT ... JOIN with NULL valuesSGreen26 Aug