Re: SELECT ... JOIN with NULL values

[SGreen]

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