DELETE ... USING permite excluir linhas de uma tabela filtrando-as contra outra, no estilo de um JOIN, sem subconsulta correlacionada. E sintaxe nativa do PostgreSQL, e varios motores oferecem a mesma ideia com pequenas variacoes.
Sintaxe basica
Suponha que voce precise excluir todos os pedidos de usuarios dos EUA. O pais fica na tabela users, mas queremos excluir de orders. A tabela de filtragem vai no USING e a condicao de ligacao no WHERE.
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
AND u.country = 'US';
Aqui orders e a tabela alvo e dela saem linhas. users entra apenas para filtrar e nunca e modificada. A clausula o.user_id = u.id faz o papel do ON em um JOIN comum, e u.country = 'US' e um filtro extra. Sao excluidas exatamente as linhas de orders que encontram par em users.
- No
USING vai a tabela pela qual voce filtra, nao aquela da qual exclui.
- A ligacao entre tabelas e um predicado comum no
WHERE, nao um ON separado.
- Os alias (
o, u) mantem a consulta legivel e sao obrigatorios quando os nomes de coluna coincidem.
Varias tabelas no USING
Voce pode listar varias tabelas no USING, separadas por virgula; elas se unem entre si e a tabela alvo pelas condicoes do WHERE. Vamos excluir pedidos ligados a gerentes do departamento de vendas.
DELETE FROM orders o
USING users u, employees e
WHERE o.user_id = u.id
AND u.id = e.manager_id
AND e.dept = 'sales';
A logica espelha um JOIN de tres tabelas: o PostgreSQL monta a juncao orders x users x employees e entao exclui cada linha de orders que sobrevive. Se uma linha de orders casa com varias linhas a direita, ela e excluida uma unica vez; as duplicatas a direita nao multiplicam a exclusao.
DELETE ... USING versus uma subconsulta WHERE IN
A forma classica de expressar a mesma coisa e uma subconsulta:
DELETE FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE country = 'US'
);
Ambas estao corretas, mas ha uma diferenca pratica:
- O
USING expoe as colunas da segunda tabela dentro do WHERE, entao filtrar por varios campos fica plano, sem aninhamento.
- Uma subconsulta com
IN retorna uma unica coluna; para comparar um par de colunas e preciso mudar para EXISTS ou construtores de linha, e a legibilidade piora.
- O planejador do PostgreSQL costuma converter ambas as formas em um semi-/hash-join, entao o desempenho e parecido. Mas o
USING fica mais claro quando voce tambem precisa de uma condicao da segunda tabela, e nao apenas de uma lista de chaves.
Pegadinha: o NOT IN com subconsulta quebra em silencio com NULL: se a subconsulta produzir ao menos um NULL, ela retorna vazio e nao exclui nada. Para "excluir pedidos de usuarios que nao estao em uma whitelist", prefira NOT EXISTS ou LEFT JOIN ... IS NULL em vez de NOT IN.
Seguranca do WHERE: o risco principal
O erro mais caro com DELETE ... USING e omitir ou estragar a condicao de ligacao. Se voce remover o.user_id = u.id, obtem um produto cartesiano, que exclui a tabela alvo inteira: cada linha de orders encontra ao menos um par em users.
DELETE FROM orders o
USING users u
WHERE u.country = 'US';
Protecoes simples:
- Transforme primeiro o
DELETE em um SELECT com o mesmo FROM/USING/WHERE e inspecione o que seria excluido.
- Envolva a operacao em uma transacao (
BEGIN; ... ROLLBACK;) ate confiar na contagem de linhas.
- Confira a contagem de linhas informada: se parecer suspeitamente alta, nao faca
COMMIT.
DELETE multitabela no MySQL
O MySQL nao tem USING nesse sentido; em vez disso oferece um DELETE multitabela com um JOIN explicito. O detalhe chave: entre DELETE e FROM voce lista de quais tabelas realmente exclui linhas.
DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';
Se escrever DELETE o, u, voce remove linhas das duas tabelas de uma vez: as vezes pratico, mas mais arriscado. No ClickHouse as exclusoes sao assincronas (ALTER TABLE ... DELETE, ou um DELETE leve em versoes recentes) e nao ha semantica de join USING. Resumindo: no PostgreSQL use DELETE ... USING, no MySQL DELETE ... JOIN, e verifique sempre o predicado de ligacao antes de executar.
DELETE ... USINGpermite excluir linhas de uma tabela filtrando-as contra outra, no estilo de umJOIN, sem subconsulta correlacionada. E sintaxe nativa do PostgreSQL, e varios motores oferecem a mesma ideia com pequenas variacoes.Sintaxe basica
Suponha que voce precise excluir todos os pedidos de usuarios dos EUA. O pais fica na tabela
users, mas queremos excluir deorders. A tabela de filtragem vai noUSINGe a condicao de ligacao noWHERE.DELETE FROM orders o USING users u WHERE o.user_id = u.id AND u.country = 'US';Aqui
orderse a tabela alvo e dela saem linhas.usersentra apenas para filtrar e nunca e modificada. A clausulao.user_id = u.idfaz o papel doONem umJOINcomum, eu.country = 'US'e um filtro extra. Sao excluidas exatamente as linhas deordersque encontram par emusers.USINGvai a tabela pela qual voce filtra, nao aquela da qual exclui.WHERE, nao umONseparado.o,u) mantem a consulta legivel e sao obrigatorios quando os nomes de coluna coincidem.Varias tabelas no USING
Voce pode listar varias tabelas no
USING, separadas por virgula; elas se unem entre si e a tabela alvo pelas condicoes doWHERE. Vamos excluir pedidos ligados a gerentes do departamento de vendas.DELETE FROM orders o USING users u, employees e WHERE o.user_id = u.id AND u.id = e.manager_id AND e.dept = 'sales';A logica espelha um
JOINde tres tabelas: o PostgreSQL monta a juncaoordersxusersxemployeese entao exclui cada linha deordersque sobrevive. Se uma linha deorderscasa com varias linhas a direita, ela e excluida uma unica vez; as duplicatas a direita nao multiplicam a exclusao.DELETE ... USING versus uma subconsulta WHERE IN
A forma classica de expressar a mesma coisa e uma subconsulta:
DELETE FROM orders WHERE user_id IN ( SELECT id FROM users WHERE country = 'US' );Ambas estao corretas, mas ha uma diferenca pratica:
USINGexpoe as colunas da segunda tabela dentro doWHERE, entao filtrar por varios campos fica plano, sem aninhamento.INretorna uma unica coluna; para comparar um par de colunas e preciso mudar paraEXISTSou construtores de linha, e a legibilidade piora.USINGfica mais claro quando voce tambem precisa de uma condicao da segunda tabela, e nao apenas de uma lista de chaves.Seguranca do WHERE: o risco principal
O erro mais caro com
DELETE ... USINGe omitir ou estragar a condicao de ligacao. Se voce removero.user_id = u.id, obtem um produto cartesiano, que exclui a tabela alvo inteira: cada linha deordersencontra ao menos um par emusers.-- DANGER: no join predicate -> deletes every row in orders DELETE FROM orders o USING users u WHERE u.country = 'US';Protecoes simples:
DELETEem umSELECTcom o mesmoFROM/USING/WHEREe inspecione o que seria excluido.BEGIN; ... ROLLBACK;) ate confiar na contagem de linhas.COMMIT.DELETE multitabela no MySQL
O MySQL nao tem
USINGnesse sentido; em vez disso oferece umDELETEmultitabela com umJOINexplicito. O detalhe chave: entreDELETEeFROMvoce lista de quais tabelas realmente exclui linhas.DELETE o FROM orders o JOIN users u ON o.user_id = u.id WHERE u.country = 'US';Se escrever
DELETE o, u, voce remove linhas das duas tabelas de uma vez: as vezes pratico, mas mais arriscado. No ClickHouse as exclusoes sao assincronas (ALTER TABLE ... DELETE, ou umDELETEleve em versoes recentes) e nao ha semantica de joinUSING. Resumindo: no PostgreSQL useDELETE ... USING, no MySQLDELETE ... JOIN, e verifique sempre o predicado de ligacao antes de executar.