Get the latest data by datetime fields from two tables

advertisements

This question is slightly different from my previous question : Get the latest data comparing datetime fields from two table

I have two tables with identical structure like this:

Table : user

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |      john123 |  2014-06-04 14:58:22 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |     marie123 |  2014-06-04 15:00:06 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
|  8 | Bruce  |     bruce123 |  2014-06-04 15:07:11 |
+----+--------+--------------+----------------------+

Table : user_k

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |       abu123 |  2014-06-04 14:53:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |  shane123NEW |  2014-06-04 15:00:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |      mike123 |  2014-06-04 15:00:36 |
|  6 | kiron  |     kiron123 |  2014-06-04 15:02:18 |
|  7 | Dan    |       dan123 |  2014-06-04 15:09:46 |
+----+--------+--------------+----------------------+

Now I need to fetch the latest data (by 'LastUpdateTime') from these two tables, like this:

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
|  7 | Dan    |       dan123 |  2014-06-04 15:09:46 |
|  8 | Bruce  |     bruce123 |  2014-06-04 15:07:11 |
+----+--------+--------------+----------------------+

What I have now:

select
u.id,
u.name,
if(u.LastUpdateTime >= k.LastUpdateTime, u.password, k.password) as password,
greatest(u.LastUpdateTime, k.LastUpdateTime) as LastUpdateTime
from
user u
inner join user_k k on u.id = k.id

But this fetches only those records that are available with both tables... I need the data that is present in any one table of the tables also. Also, I'm concerned about the query performance with large set of data.

Here is the sqlfiddle

PS: Besides name & password there are a dozen more fields(columns) in both tables. I avoid them just to simplify the question.


SELECT x.*
  FROM
     ( SELECT * FROM user
       UNION ALL
       SELECT * FROM user_k
     ) x
  JOIN
     ( SELECT id,MAX(lastupdatetime) max_lut
         FROM
            ( SELECT * FROM user
               UNION ALL
              SELECT * FROM user_k
            ) a
        GROUP
           BY id
     ) y
    ON y.id = x.id
   AND y.max_lut = x.lastupdatetime
 ORDER
    BY id;

http://sqlfiddle.com/#!2/05d8be/14