0
CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT 
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField2) "uniqueFieldName2")
    --more of these if statements with the same condition ,but different field
FROM table1 
--various joins with other tables
);

I was wondering if following principles of DRY (don't repeat yourself) there would be a means of storing the same boolean condition being use in the IF statements for reuse to clean up the code and possibly make the query run better.

I have tried setting a boolean variable

set booleanIfCondition = table1.foo or table2.foo IN ('State1', 'State2');

However I get syntax errors whether I place it before the if statement or try to sneak in somewhere after the select.

1
  • Any attempt to optimize common subqueries will probably lead to a slower overall query due to the cost of temp table, etc. – Rick James Nov 20 '19 at 22:21
1

Test

CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT 
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(@tmp := (table1.foo or table2.foo IN ('State1', 'State2')), 0, table3.uniqueField) "uniqueFieldName",
    IF(@tmp, 0, table3.uniqueField2) "uniqueFieldName2",
    --more of these if statements with the same condition ,but different field
FROM table1 
--various joins with other tables
);
3
  • 1
    Fewer keystrokes, but does it run any faster? – Rick James Nov 20 '19 at 22:22
  • 1
    @RickJames does it run any faster? Must be tested. – Akina Nov 21 '19 at 8:59
  • For a SELECT statement this will work, however inside a VIEW it will not dev.mysql.com/doc/refman/5.6/en/create-view.html as VIEWs aren't particularly friendly to variables unfortunately. – user1821961 Nov 22 '19 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.