Full outer join
Hello, I like to have a full outer join. if you have the following tables: t1: id | val 1 | A 2 | B t2: id | val 1 | B 2 | C SELECT t1.id, t2.id, t1.val FROM t1 FULL OUTER JOIN t2 ON t1.val=t2.val ORDER BY t1.id,t2.id I want to get the following result (and in this order): t1.id | t2.id | t1.val 1 | NULL | A 2 | 1 | B NULL | 2 | C As far as I can tell this is not possible in MySQL. Is there a way to construct a MySQL statement that produces this result? Maurice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full outer join
Maurice van Peursem wrote: Hello, I like to have a full outer join. if you have the following tables: t1: id | val 1 | A 2 | B t2: id | val 1 | B 2 | C SELECT t1.id, t2.id, t1.val FROM t1 FULL OUTER JOIN t2 ON t1.val=t2.val ORDER BY t1.id,t2.id I want to get the following result (and in this order): t1.id | t2.id | t1.val 1 | NULL | A 2 | 1 | B NULL | 2 | C As far as I can tell this is not possible in MySQL. Is there a way to construct a MySQL statement that produces this result? Maurice Yes, you can produce this result using the union of two left joins: (SELECT t1.id AS 't1_id', t2.id AS 't2_id', t1.val FROM t1 LEFT JOIN t2 ON t1.val=t2.val) UNION DISTINCT (SELECT t1.id AS 't1.id', t2.id AS 't2.id', t2.val FROM t2 LEFT JOIN t1 ON t1.val=t2.val) ORDER BY t1_id IS NULL, t1_id, t2_id IS NULL, t2_id; Note I added checks for NULL in the ORDER BY clause, because NULLs ordinarily come first, but you wanted them last. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL OUTER JOIN
[EMAIL PROTECTED] wrote on 04/04/2005 01:14:23 PM: Hello all, mysql 4.0.20 I'd like to know how one can do a full outer join. I've read some workaround with a UNION, but i need the join only on a few columns, while UNION will make double tuple if one column is not the same. I also would like to avoid temporary table if possible, since the query is on many millions of rows, and i saw performances suffer when working on multiple temporary tables. If anyone has an idea, he would be great. Thanks you Vincent Sorry Vincent, The only way I can think to make it work is through a UNION. I don't fully trust RIGHT JOINS (on any system) so I use two LEFT JOINs but flip the order on the second table. Basically this is what the engine has to do to compute a FULL OUTER JOIN anyway but you have to write the code externally instead of it being encapsulated within the query engine.7 ( SELECT column list FROM table a LEFT JOIN table b ON a.keycol = b.keycol ) UNION ( SELECT column list FROM table b LEFT JOIN table a ON b.keycol = a.keycol WHERE a.keycol is null ) The UNION will only return the columns you ask it to. This method also avoids manually creating a temporary table. So long as you do not say UNION ALL, you won't get any duplicate rows. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
FULL OUTER JOIN
Hello all, mysql 4.0.20 I'd like to know how one can do a full outer join. I've read some workaround with a UNION, but i need the join only on a few columns, while UNION will make double tuple if one column is not the same. I also would like to avoid temporary table if possible, since the query is on many millions of rows, and i saw performances suffer when working on multiple temporary tables. If anyone has an idea, he would be great. Thanks you Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full outer join?
According to the MySQL documentation, it doesn't currently support full outer joins and won't do in the immediate future. Does anyone have any suggestions for a workaround? I need to join three tables, only one of which has a common identifier with the others, and produce a result which shows not only the matching information (eg, where A.first_id = B.first_id and A.second_id = C.second_id), but also find any rows in either B or C that don't have a matching row in A, as well as any rows in A that don't match either B or C. Starting with A, I can easily show rows that don't have a corresponding match in B or C by means of a left join, but I can't work out how to get the reverse information in the same select. Any ideas? Or am I missing something obvious? Mark -- With sufficient thrust, pigs fly just fine. From RFC1925 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
