A.5.4 Problems with alias

You can use an alias to refer to a column in the GROUP BY, ORDER BY, or in the HAVING part. Aliases can also be used to give columns better names:

SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;

Note that standard SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. For example, the following query is illegal:

SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be included in the GROUP BY part while HAVING is used to decide which rows from the result set should be used.

User Comments

Can someone tell me how to work around the sub-select that is not possible in MYSQL 3.23.x just in MYSQL? I know that it is possible with JAVA/PERL/PHP/..-scripts and arrays-variables, but I want to avoid these scriptings!

# EXAMPLE TABLES AND VALUES
drop table if exists more, less;
create table more (id integer(6));
create table less (id integer(6));
insert into more values (1), (2), (3), (4), (5), (6), (7), (8);
insert into less values (1), (2), (3), (4);

# SELECT BEFORE
select id as more from more;
#+------+
#| more |
#+------+
#| 1 |
#| 2 |
#| 3 |
#| 4 |
#| 5 |
#| 6 |
#| 7 |
#| 8 |
#+------+
select id as less from less;
#+------+
#| less |
#+------+
#| 1 |
#| 2 |
#| 3 |
#| 4 |
#+------+

#NOT POSSIBLE WITH MYSQL 3.23.x:
#delete from more where id not in (select id from less);

#WITH HARD-CODED VALUES IT WOULD BE:
delete from more where id not in (1, 2, 3, 4);

# SELECT AFTER
select id as more from more;
#+------+
#| more |
#+------+
#| 1 |
#| 2 |
#| 3 |
#| 4 |
#+------+

select id as less from less;
#+------+
#| less |
#+------+
#| 1 |
#| 2 |
#| 3 |
#| 4 |
#+------+

Suppose you have the following SQL script in t.sql:
select 'begin;';
select concat('delete from tableX where tableX.id=',tableY.id,';')
from tableY where <conditions>;
select 'commit;';

Execute it (make sure to specify -BN switches):
mysql.exe -BN -u %uname% -p"%pwd%" -h %host% -P%port% dbname < t.sql > t1.sql

Now t1.sql has statements to delete certain records...
Wrap this sequence in a shell script.

Probably this is not the best solution but works fine for me. :)

Add your own comment.