1.9.4.1 Вложенные SELECTы

В сервер MySQL поддерживает вложенные запросы вида INSERT ... SELECT ... и REPLACE ... SELECT .... В других контекстах можно использовать и функцию IN().

Вложенные операции выборки реализованы в версии 4.1.

Между тем, во многих случаях можно переписать запрос, чтобы не использовать вложенную выборку. Например, запрос:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

можно переписать следующим образом:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

Запросы:

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);

эквивалентны следующему:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
                WHERE table2.id IS NULL;

Для более сложных подзапросов часто можно создать временные таблицы, содержащие данный подзапрос. Иногда, однако, этот способ не годится, чаще всего для команд DELETE, для которых в стандарте SQL не поддерживаются объединения (за исключением вложенных выборок). В этой ситуации возможны два временных (пока вложенные запросы не поддерживаются сервером MySQL) варианта решения проблемы.

Первый вариант следующий: при помощи какого-либо процедурно-ориентированного языка программирования (такого как Perl или PHP) делается запрос SELECT для получения первичных ключей тех записей, которые должны быть удалены, а затем полученные величины используются для составления команды DELETE (DELETE FROM ... WHERE ... IN (key1, key2, ...)).

Второй вариант предполагает применение диалогового SQL для автоматического создания набора команд DELETE с использованием расширения MySQL CONCAT() (вместо стандартного оператора ||). Например:

SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';')
       FROM tab1, tab2
       WHERE tab1.col1 = tab2.col2;

Можно поместить этот запрос в файл скрипта, перенаправить стандартный вход клиента командной строки с этого файла, а стандартный выход - на еще один экземпляр клиента командной строки:

shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb

Сервер версии MySQL 4.0 поддерживает многотабличные удаления - эту функцию можно использовать для эффективного удаления строк как из одной таблицы, так и из нескольких одновременно

User Comments

Deletes and sub-selects.

I have found a convenient way of avoiding sub-
selects for deleting as follows -
1/ Add a DELETE_FLAG column to the table involved.
2/ Set the DELETE_FLAG using UPDATE, where joins
can be used.
3/ DELETE from the table using a simple WHERE
clause to select rows where the DELETE_FLAG is
set.

Tip: if you use the DELETE_FLAG approach you will
most likely lose the ability to add UK's as
you'll get UK violations when you try to delete
the same logical record a second time.

The Debian package of 3.23 only worked when a list
of values was used so I wrote a function to convet
the sub-select result into a string. This is a
function I used. It's easier than
rewriting loops. I hope it may be useful to someone.


This was for PHP4.

I needed to know how many tickets were owned by a
department, so the call was:


$deptname = genericget($deptid, deptid, deptname,
department);
$querytickets = "SELECT * from ticket WHERE owner
IN ("
. getnestedvals(id, user, dept_id, $deptid) . ")";
$result = mysql_query($querytickets)
or die("MySQL Error: " . mysql_error() . "
$querytickets.");
$numtickets = mysql_num_rows($result);
echo "There are $numtickets tickets owned by
$deptname.";


// This is a generic function which returns the
results
// of a query in list form, so they can be used in
place
// of nested queries until it is implemented in
MySQL
// If there are no values, it returns the string
"Empty Nested Set" by default.

function getnestedvals($field, $table,
$comparefield, $key)
{
$nestedquery = "SELECT $field FROM $table WHERE
$comparefield = '$key'";
$nestedresult = mysql_query($nestedquery)
or die("MySQL Error: " . mysql_error() . "
$nestedquery");
if (mysql_num_rows($nestedresult) == 0)
{
return "Empty Nested Set";
}
else
{
$row = mysql_fetch_array($nestedresult);
$nestedvals = $row[$field];
while ($row =
mysql_fetch_array($nestedresult))
{
$nestedvals = $nestedvals . ",
$nestedvals";
}
return $nestedvals;
} // end of else numrows > 0
} // end of getnestedvals()

For deleting like subselect you can often use construction like as listed below
> SET @list:='';
> SELECT LEFT(@list:=CONCAT(@list, t1.id, ','), -1) FROM table1 AS t1, table2 AS t2 WHERE <conditions>
> DELETE FROM table1 WHERE id IN (LEFT(@list, LENGTH(@list)-1));
--------------------
Of course this will works only if you have key field in the table. If no matching rows will found by the 2-nd query, the 3-rd query will fails. You can specify for example SET @list='-1,', if your key have no -1 value.

(ORIGINALLY POSTED UNDER CHAPTER 5.2.6 by David Martin on Friday January 24 2003, @10:18am)

This gave me trouble for a while so I thought I'd
pass it on. If you want a left join with a
condition for the right table (the row that may
or may not be there) you have to put the
condition in the join clause, not the where
clause.

Here's an example:
SELECT
*
FROM
t1 LEFT JOIN t2 ON
(t1.id_t1 = t2.id_t1 AND t2.id_t2 = 345)
WHERE
t1.id_level=17;

I appologize if that's common knowledge but this
note would have saved me hours.
Thanks

Add your own comment.