Mysql no soporta subconsultas bajo esa sintax. Está previsto el tema de sub.consultas para las versioens 4.1.x en adelante.
Para la versión de Mysql que usaras (supongo que una 3.x o 4.0.x) revisa el manual oficial de Mysql para ver como se hace aquí:
Subconsultas para Mysql versiones 3.x y 4.0.x:
http://www.mysql.com/doc/en/Rewriting_subqueries.html
Esto es como se harán las subconsultas en Mysql 4.1.x en adelante (está en fase alpha todavía .. ojo):
http://www.mysql.com/doc/en/Subqueries.html
Lo mismo ocurre con los "Triggers" y "Store procedure" .. Mysql no dispone de esas funcionalidades.
Por lo demás .. si bien el SQL debería ser standard .. no todos los manejadores de Base de datos usan o siguen el standard o lo implementan al 100% .. así que antes de nada deberías leer las características técnicas del motor de base de datos que vas a trabajar para ver si es lo que necesitas o si las alternativas que tiene para resolver un problema puntual te acomodan.
mas info:
http://www.mysql.com/doc/en/Differences_from_ANSI.html
Un saludo,