Hello Mathias!
I know what is the difference between LEFT and INNER JOIN.
I need the NULL records too.
mysql> select * from a left join b on a.id=b.id;
+------+---------+------+---------+
| id | txt | id | txt |
+------+---------+------+---------+
| 1 | a test1 | 1 | b test1 |
| 2 | a test2 | 2 | b test2 |
| 3 | a test3 | NULL | NULL |
+------+---------+------+---------+
That is OK, but very slow.. :(
Best Regards!
Zoltán
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Jigal van Hemert" <[EMAIL PROTECTED]>
Cc: "Gyurasits Zoltán" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Monday, June 13, 2005 10:21 AM
Subject: Re: Optimises LEFT JOIN
Hi zoltan
if you know difference between inner join and left join, you can conclude
than
maybe there are a lot of rows in table1 which do not verify you join
condition.
This example show you that the result is different and and add'in where
clause
if you want to transform the left join result like then inner join one :
mysql> select * from a;
+------+---------+
| id | txt |
+------+---------+
| 1 | a test1 |
| 2 | a test2 |
| 3 | a test3 |
+------+---------+
3 rows in set (0.03 sec)
mysql> select * from b;
+------+---------+
| id | txt |
+------+---------+
| 1 | b test1 |
| 2 | b test2 |
+------+---------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from a inner join b on a.id=b.id;
+------+---------+------+---------+
| id | txt | id | txt |
+------+---------+------+---------+
| 1 | a test1 | 1 | b test1 |
| 2 | a test2 | 2 | b test2 |
+------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from a left join b on a.id=b.id;
+------+---------+------+---------+
| id | txt | id | txt |
+------+---------+------+---------+
| 1 | a test1 | 1 | b test1 |
| 2 | a test2 | 2 | b test2 |
| 3 | a test3 | NULL | NULL |
+------+---------+------+---------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from a left join b on a.id=b.id where b.txt is not null;
+------+---------+------+---------+
| id | txt | id | txt |
+------+---------+------+---------+
| 1 | a test1 | 1 | b test1 |
| 2 | a test2 | 2 | b test2 |
+------+---------+------+---------+
2 rows in set (0.00 sec)
Hope that helps.
Mathias
Selon Jigal van Hemert <[EMAIL PROTECTED]>:
From: "Gyurasits Zoltán"
> I have a speed problem with LEFT JOIN condition.
(...)
> Why?? Somebody can help me? Tnx!!
You will probably get a lot more useful response if you include the table
structure, the complete query/queries and the output of EXPLAIN for each
query (just put EXPLAIN in front of the SELECT. It will give you
information
about the execution path that MySQL came up with).
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
