Can I apply a condition in a where clause on a field that I have selected via IF?

I'll give you an example because it's hard to understand what I'm talking about:

   SELECT a.f1, 
          b.f1, 
          IF(a.f3 <> '', a.f3, b.f3) as f3 
     FROM a 
LEFT JOIN b 
       ON b.f4 = a.f4 
    WHERE f3 = 'something'

      

Now this won't work because I cannot have an alias as a field in the where clause, so how can I apply a condition to that f3 field which is either from a or b. Preferably not putting the IFs bundle in the where clause

+3


source to share


3 answers


Try the following:

SELECT * FROM (
    SELECT a.f1, b.f1,
      IF(a.f3 <> '', a.f3, b.f3) as f3
    FROM a 
    LEFT JOIN b on b.f4 = a.f4
) S
WHERE S.f3 = 'something'

      



This is actually less efficient than replacing the value with the formula itself, as it would require a full scan. To improve performance (but decrease readability), you can use this query:

SELECT a.f1 aF1, b.f1 bF1,
  IF(a.f3 <> '', a.f3, b.f3) as f3
FROM a 
LEFT JOIN b on b.f4 = a.f4
WHERE IF(a.f3 <> '', a.f3, b.f3) = 'something'

      

+1


source


WHERE will not work, but it will.

SELECT
a.f1,
b.f1,
IF(a.f3 <> '', a.f3, b.f3) as fc3
FROM 
a 
LEFT JOIN b on b.f4 = a.f4
HAVING fc3 = 'something'

      



i renamed to countable field to avoid potential collision of column names

+3


source


Use logic.

 SELECT a.f1, 
          b.f1, 
          IF(a.f3 <> '', a.f3, b.f3) as f3 
     FROM a 
LEFT JOIN b 
       ON b.f4 = a.f4 
    WHERE a.f3 = 'something' OR (a.f3 == '' AND b.f3 = 'something')

      

+2


source







All Articles