Select from table if exists, else select from a totally different table

Question

I have a query to fetch two values:

string query = @"SELECT price, weight
                 FROM   map 
                 WHERE  width = @width AND height = @height LIMIT 1";

if (_connSource.State != ConnectionState.Open)
    _connSource.Open();
MySqlCommand cmd = new MySqlCommand(query, _connSource);
cmd.Parameters.AddWithValue("width", width);
cmd.Parameters.AddWithValue("height", height);
r = cmd.ExecuteReader();

if (!r.Read())
{
    r.Close();

    query = @"SELECT retail_price, 0
              FROM   globe
              WHERE  PK_Id = @PK_Id LIMIT 1"

    cmd = new MySqlCommand(query, _connSource);
    cmd.Parameters.AddWithValue("PK_Id ", 1);
    r = cmd.ExecuteReader();
 }

What I need is to get price and weight according to a condition, but if it is not present in the table, then I need to get another two fields retail_price, and a constant 0 (doesnt matter what it is) from a totally new table with no constraints from the previous table. Can I get the two in a single query?

Note: Kindly give me optimized queries which doesn't force reading the same values more than once (this function gets executed thousands of times in one single operation, so speed is very critical - a reason why I'm trying to get it in one query). Thanks..

Answer 1

I did get a pretty hacky solution to this, accidentally. Here is how one can do it:

SELECT price, weight
FROM map
WHERE width = @width AND height = @height LIMIT 1

UNION ALL

SELECT retail_price, 0
FROM  globe
WHERE PK_Id = @PK_Id LIMIT 1

The LIMIT 1 clause actually limits it from reading the second value if I get an answer in first select itself. Notice that I haven't added parentheses anywhere so that MySQL doesn't treat this as normal UNION ALL. Read more relevant info here and here

Apparently you can do this to make it more meaningful with this, but I do not think it performs any better:

SELECT price, weight
FROM map
WHERE width = @width AND height = @height LIMIT 1

UNION ALL

(SELECT retail_price, 0
FROM  globe
WHERE PK_Id = @PK_Id LIMIT 1) LIMIT 1

Here since I have added brackets, it works like normal UNION ALL, that is I should get both records if possible, but the last LIMIT 1 clause at the end of the query limits the result to the first set.