List:General Discussion« Previous MessageNext Message »
From:Nuno Pereira Date:August 26 2005 6:08pm
Subject:SELECT ... JOIN with NULL values
View as plain text  
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
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