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..

score 3 · Answer 1 · edited Apr 19 '12 at 03:50

3

The syntax gets a little ugly, but I think you can do it:

(SELECT price, weight
 FROM map 
 WHERE width = @width AND height = @height LIMIT 1)
UNION ALL
(SELECT retail_price as price, 0 as weight
 FROM  globe
 WHERE PK_Id = @PK_Id LIMIT 1)

This returns 0 to 2 rows. If 2 rows are returned, pick the 1st one.

EDIT You can try avoiding the cost of the second query by using this monstrous construct. I am not sure if MySQL is going to handle it well, but it has a decent chance of avoiding the second query:

select
    ifnull(price, (select retail_price from globe where PK_Id = @PK_Id LIMIT 1))
,   ifnull(weight, 0)
from map 
WHERE width = @width AND height = @height LIMIT 1

edited Apr 19 '12 at 03:50

nawfal

70,104
56
326
368

answered Apr 17 '12 at 14:22

Sergey Kalinichenko

714,442
84
1,110
1,523

I do not have an instance of mysql handy to test this - let me know if it does not work, and I'll remove the answer to make your question look brand-new again. – Sergey Kalinichenko Apr 17 '12 at 14:22
This is kinda workaround. The problem with this query I see is that the second select statement is executed at all costs which becomes redundant if we already got an answer with the first select. I pointed out in the question that optimization is mighty important – nawfal Apr 17 '12 at 14:31
@nawfal This is certainly true, although if I understand it right that `PK_ID` is the primary key, the second query is a lot cheaper than the first one, and `LIMIT 1` does not even come into play. This should beat an additional round-trip to the database hands down, unless your first query returns an answer most of the time. – Sergey Kalinichenko Apr 17 '12 at 14:35
Yes the second query is cheap, but still.. My first select, yes mostly delivers an output. But thanks for the different approach – nawfal Apr 17 '12 at 14:39
@nawfal I added my second attempt. If it works, please don't tell anybody that I wrote that ugly monster :) – Sergey Kalinichenko Apr 17 '12 at 14:57
dead on. and cuter too (how the hell is this monstrous!) But I feel its a straight lift from @k0alaDEV's answer (kidding) :) Since he came up with this solution first, I'm obliged to accept his :) – nawfal Apr 17 '12 at 15:00
The problem with the IFNULL is that it works if a particular field is "NULL" and not if the result of a select is empty! :( IFNULL is entirely a different thing I mean – nawfal Apr 18 '12 at 05:15

score 1 · Answer 2 · edited Apr 17 '12 at 18:08

1

If there is no Resultrow to the map WHERE then the DBNull will be replaced by globe values

SELECT 
    price = IFNULL(map.price,gl.price), 
    weight = IFNULL(map.weight,0)
FROM   map 
LEFT JOIN globe gl ON PK_Id = @PK_Id    
WHERE  width = @width AND height = @height 
LIMIT 1

edited Apr 17 '12 at 18:08

Gilles 'SO- stop being evil'

104,111
38
209
254

answered Apr 17 '12 at 14:36

k0alaDEV

61
1
9

this doesnt yield the right result. if `width = @width AND height = @height` condition is not met all, then an empty result set is returned and not the values in globe. Moreover it may retrieve `m.price` and `g.weight` as a set or `m.weight` and `g.price` as a set according to null values which is undesired. How I do want is `m.price` and `m.weight` if exists according to a condition, if it doesnt then I want `g.price` and `g.weight`. Hope its clear. Also `IFNULL` does a different thing altogether and not check if its empty – nawfal Apr 18 '12 at 06:05

score 1 · Accepted Answer · edited May 23 '17 at 11:55

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.

score 0 · Answer 4 · answered Apr 17 '12 at 14:35

0

Since your "limit" clause guarantees a maximum of one row from each query, you could use a left join, and deal with existence of the "map" record using "IF()":

select ifnull(m.price,g.price), ifnull(m.weight,g.weight) from
(SELECT retail_price as price, 0 as weight
 FROM  globe
 WHERE PK_Id = @PK_Id LIMIT 1) g
LEFT JOIN
(SELECT price, weight
 FROM   map 
 WHERE  width = @width AND height = @height LIMIT 1) m
ON 1=1

The above query assumes that price and weight in the map table cannot be null.

answered Apr 17 '12 at 14:35

aingram

446
2
9

1

This has the same points as the post from @dasblinkenlight in that it always queries both tables. However, this one guarantees only one record. – aingram Apr 17 '12 at 14:40
the problem with above query, apart from the one u mentioned, is that it retrieves `m.price` and `g.weight` as a set or `m.weight` and `g.price` as a set according to null values which is undesired. How I do want is `m.price` and `m.weight` if exists according to a condition, if it doesnt then I want g.price and g.weight. Hope its clear. – nawfal Apr 18 '12 at 05:48
moreover IFNULL does a different thing altogether compared to something like `ifempty` – nawfal Apr 18 '12 at 05:58

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

4 Answers4