# 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

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