How to join tables to get one row for each row of source tables?

Question

I have two different kinds of entities in two tables with only a few common columns that I want to join and sort by one of the common columns. In the resulting table each row should represent either a row from one table or a row from another. For example, for the tables like this:

CREATE TABLE apple (
    apple_id INT NOT NULL AUTO_INCREMENT,
    acquired DATETIME NOT NULL,
    is_fresh TINYINT,
    PRIMARY KEY (apple_id)
);

'1', '2012-07-27 19:06:58', '1'
'2', '2012-07-28 18:05:43', '0'

CREATE TABLE orange (
    orange_id INT NOT NULL AUTO_INCREMENT,
    acquired DATETIME NOT NULL,
    is_yummy TINYINT,
    PRIMARY KEY (orange_id)
);

'1', '2012-06-12 10:03:31', '0'
'2', '2012-08-30 22:00:02', '1'

My idea of query for joining them would be like this:

SELECT
    IF(orange_id IS NULL, 'apple', 'orange') AS type,
    IF(orange_id IS NULL, apple.acquired, orange.acquired) AS acquired,
    is_fresh,
    is_yummy
FROM apple CROSS JOIN orange ON apple_id IS NULL OR orange_id IS NULL
ORDER BY acquired ASC

'orange', '2012-06-12 10:03:31', NULL, '0'
'apple', '2012-07-27 19:06:58', '1', NULL
'apple', '2012-07-28 18:05:43', '0', NULL
'orange', '2012-08-30 22:00:02', NULL, '1'

I see a few problems with this query:

1. If I understand how CROSS JOIN works, the query will be very slow, as it will go through every possible apple-orange combination, will it not? Is there a faster way?

2. The IF(...) functions look ugly. Are they really necessary?

Answer 1

I am not sure whether this is a solution for your problem. But you can make similar output using an union. Comparatively union will be faster that Cross join.