More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Functionality Missing from MySQL - Sub-selects
The following will not yet work in MySQL:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);
However, in many cases you can rewrite the query without a sub-select:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL
For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however this
option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL
does not support joins (except in sub-selects). For this situation there are two options available until subqueries are
supported by MySQL.
The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the
primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ...
WHERE ... IN (key1, key2, ...)).
The second option is to use interactive SQL to contruct a set of DELETE statements automatically, using the MySQL extension
CONCAT() (in lieu of the standard || operator). For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its
output back to a second instance of the interpreter:
prompt> mysql --skip-column-names mydb > myscript.sql | mysql mydb
MySQL only supports INSERT ... SELECT ... and REPLACE ... SELECT ... Independent sub-selects will probably be available in
Version 4.0. You can now use the function IN() in other contexts, however.
(Continued on next question...)
