Sebastian Tobias Mendel genannt Mendelsohn <lists@stripped> wrote:
>>>SELECT product_id, name, description, sales.sale_id
>>>FROM products LEFT JOIN sales ON products.product_id = sales.product_id
>>>WHERE sales.customer_id = 10 AND sales.sale_id IS NULL
>>
>>
>> This query should return no rows, because if you retrieve rows where
> sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10.
>
>
> you are wrong, or do you know the table-structure?
No, I don't know table structure.
> sales.sale_id can be NULL while customer_id can be 10 !
Probably you misundernstood me. Look at the following example, there are 2 test table: t1
and t2.
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | vita |
| 3 | egor |
| 5 | tony |
+------+------+
3 rows in set (0.00 sec)
Now I want to do this simple SELECT statement that is like author want to do:
SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2 WHERE name='egor').
For versions before 4.1 I can rewrite NOT IN() using LEFT JOIN.
Here is the output of LEFT JOIN:
mysql> select * from t1 left join t2 on t1.id=t2.id;
+------+------+------+
| id | id | name |
+------+------+------+
| 1 | 1 | vita |
| 2 | NULL | NULL |
| 3 | 3 | egor |
| 4 | NULL | NULL |
| 5 | 5 | tony |
+------+------+------+
5 rows in set (0.01 sec)
As you can see, if I add condition t2.id IS NULL to the WHERE clause, I can't retrieve
rows where name='egor'. That is why author didn't get any rows.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
