MySQL - using REGEXP on an alias of a subquery

Question

How can I use REGEXP on an alias of a subquery?

SELECT 
    colA,
    colB,
    (SELECT colA FROM t2 WHERE t1.colID=t2.colID) as colC
FROM t1
WHERE colC REGEXP '$string'
ORDER BY IF($col = '' OR $col IS NULL,1,0),$col";

It works if I make another select of the complete query, but I see that this costs some time

SELECT * FROM (
    SELECT 
        colA,
        colB,
        (SELECT colA FROM t2 WHERE t1.colID=t2.colID) as colC
    FROM t1
) as temp
WHERE colC REGEXP '$string'
ORDER BY IF($col = '' OR $col IS NULL,1,0),$col";

REGEXP works if I use a join and referencing the table field directly, but the order doesn't work with an alias either

SELECT 
    t1.colA AS colA,
    t1.colB AS colB,
    t2.colA AS colC
FROM t1
LEFT JOIN t2 ON t1.colID=t2.colID
WHERE t2.colA REGEXP '$string'
ORDER BY IF($col = '' OR $col IS NULL,1,0),$col";

So is a select on the complete query the only way to do what I need?

In general, the `WHERE` clause of a given scope cannot see aliases defined in its `SELECT` list (see [this question](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql)). The join is likely to be a lot more performant than the subselect anyway, if you use the `table.column` instead of the alias. — Michael Berkowski, Mar 07 '14 at 18:43
Can you explain why your last example doesn't work? (FWIW: I think the LEFT JOIN is not needed because of the WHERE clause. You could write the LEFT JOIN and WHERE as `JOIN t2 ON t1.colID=t2.colID AND t2.colA REGEXP '$string'`) — AgRizzo, Mar 07 '14 at 20:11
It seems that the alias can't be used in the IF clause. I use the Left Join because not all rows have a join entry and I want all rows from table1. — user3304232, Mar 07 '14 at 21:40

score 0 · Answer 1 · answered Mar 07 '14 at 21:16

You can use a HAVING clause (this is not standard SQL - MySQL feature):

SELECT 
    colA,
    colB,
    (SELECT colA FROM t2 WHERE t1.colID=t2.colID) as colC
FROM t1
HAVING colC REGEXP '$string'
ORDER BY IF($col = '' OR $col IS NULL,1,0),$col";

There are performance implications - your join is a better query.

MySQL - using REGEXP on an alias of a subquery

1 Answers1