I flipped through the MySQL Cookbook and jotted down anything I didn’t know and thought might be useful. There was a lot of stuff I didn’t know and thought was probably useless, especially if you do most of your data munging in a programming language outside of pure SQL. But there was still some pretty cool stuff in there. Here are five things that stood out to me.

Note: to run the queries below, you’ll need the EMP and DEPT tables for MySQL.

Inline view subqueries

Apparently these came to MySQL in version 4.1 (from the land of Oracle). I’m not quite sure what I’d really use it for, since I rarely alias column names, but it’s neat to know you can substitute a from clause with a whole query. Note the strange requirement that you have to alias the subquery, hence the seemingly out of place “x”:

select * 
from (select sal as salary, comm as commission from emp) x 
where salary < 5000;

CASE expression

How have I not stumbled upon this before? I’ve always just used the if() function. I guess this is SQL-proper way of doing the same. MySQL’s docs on using case are here.

select ename, 
sal, 
case 
when sal <= 2000 then 'underpaid' 
when sal >= 4000 then 'overpaid' 
else 'ok' 
end as status 
from emp;

Create table as select

This looks super super handy. The first query just copies the table’s structure, the second copies structure and data.

create table dept_2 as select * from dept where 1=0;
create table dept_2 as select * from dept;

Deleting duplicate records

Clever way of using a subquery with a group by to delete duplicate records. I like this, but it has the problem of not being immediately transparent about what’s being accomplished.

delete from dupes 
where id not in (select min(id) from dupes group by name)

group_concat()

This MySQL-specific function has to be seen to be believed. It allows you to take your normalized one-to-many relationships and string them back together together in a comma-separated list. MySQL’s docs on using group_concat() are here.

select deptno, 
group_concat(ename order by empno separator ',') as emps 
from emp 
group by deptno;