Diferença entre a visualização inline e a cláusula WITH?

Existem algumas diferenças importantes entre as visualizações inline (tabelas derivadas) e a cláusula WITH (CTE) no Oracle. Alguns deles são bastante universais, ou seja, são aplicáveis ​​a outros RDBMS.

1. WITHpode ser usado para construir subconsultas recursivas, inline view -not (até onde eu sei, o mesmo é para todos os RDBMS que suportam CTE)
2. É mais provável que a subconsulta na WITHcláusula seja executada fisicamente primeiro; em muitos casos, escolher entre WITHuma visualização em linha faz com que o otimizador escolha diferentes planos de execução (acho que é específico do fornecedor, talvez até específico da versão).
3. A subconsulta WITHpode ser materializada como uma tabela temporária (não sei se algum outro fornecedor além da Oracle oferece suporte a esse recurso).
4. A subconsulta em WITHpode ser referenciada várias vezes, em outras subconsultas e na consulta principal (verdadeiro para a maioria dos RDBMS).

Outras respostas cobrem muito bem as diferenças de sintaxe, então não vou entrar nisso. Em vez disso, esta resposta cobrirá apenas o desempenho no Oracle.

O otimizador Oracle pode optar por materializar os resultados de um CTE em uma tabela temporária interna. Ele usa uma heurística para fazer isso em vez de otimização baseada em custo. A heurística é algo como "Materialize o CTE se não for uma expressão trivial e o CTE for referenciado mais de uma vez na consulta". Existem algumas consultas para as quais a materialização melhorará o desempenho. Existem algumas consultas para as quais a materialização degradará drasticamente o desempenho. O exemplo a seguir é um pouco artificial, mas ilustra bem o ponto:

Primeiro crie uma tabela com uma chave primária que contenha inteiros de 1 a 10000:

CREATE TABLE N_10000 (NUM_ID INTEGER NOT NULL, PRIMARY KEY (NUM_ID));

INSERT /*+APPEND */ INTO N_10000
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10000
ORDER BY LEVEL;

COMMIT;

Considere a seguinte consulta que usa duas tabelas derivadas:

SELECT t1.NUM_ID
FROM 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t1
LEFT OUTER JOIN 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Podemos analisar essa consulta e determinar rapidamente que ela não retornará nenhuma linha. A Oracle deve ser capaz de usar o índice para determinar isso também. Na minha máquina, a consulta termina quase instantaneamente com o seguinte plano:

Eu não gosto de me repetir, então vamos tentar a mesma consulta com um CTE:

WITH N_10000_CTE AS (
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Aqui está o plano:

Esse é um plano muito ruim. Em vez de usar o índice, o Oracle materializa 10.000 X 10.000 = 10.000.000 linhas em uma tabela temporária apenas para retornar 0 linhas. O custo deste plano é em torno de 6 M o que é muito superior ao da outra consulta. A consulta levou 68 segundos para terminar na minha máquina.

Observe que a consulta pode ter falhado se não houver memória suficiente ou espaço livre no tablespace temporário.

Eu posso usar a INLINEdica não documentada para impedir que o otimizador materialize o CTE:

WITH N_10000_CTE AS (
  SELECT /*+ INLINE */ n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Essa consulta é capaz de usar o índice e termina quase instantaneamente. O custo da consulta é o mesmo de antes, 11. Assim, para a segunda consulta, a heurística usada pelo Oracle resultou na escolha de uma consulta com um custo estimado de 6 M em vez de uma consulta com um custo estimado de 11.

Para SQL Server, WITH CTEespecifica o conjunto de resultados nomeado temporário, mas é necessário apenas para o primeiro arquivo CTE. ou seja

WITH CTE AS (SELECT .... FROM), 
CTE2 AS (SELECT .... FROM)

SELECT CTE.Column, CTE2.Column
FROM CTE
INNER JOIN CTE2 on CTE.Column = CTE2.Column

Mas esta não é uma subconsulta ou subconsulta correlacionada. Há coisas que você pode fazer com um CTE que você não pode fazer com uma subconsulta no SQL Server, como atualizar as Tabelas referenciadas em um CTE. Aqui está um exemplo de atualização de uma tabela com um CTE.

Uma subconsulta seria algo como

SELECT
   C1,
   (SELECT C2 FROM SomeTable) as C2
FROM Table

Ou uma subconsulta correlacionada é o que você forneceu em seu OP se você fosse referenciar/juntar/limitar seus resultados com base em a.c1.

Portanto, eles definitivamente não são a mesma coisa, embora em muitos casos você possa usar um ou mais desses métodos para obter o mesmo resultado. Depende apenas de qual é o resultado final.

A principal diferença entre witha cláusula e uma subconsulta no Oracle é que você pode fazer referência a uma consulta dentro da cláusula várias vezes. Você pode fazer algumas otimizações com ele, como transformá-lo em uma tabela temporária usando materializedica. Você também pode fazer consultas recursivas com ele referenciando-se dentro de uma withcláusula. Você não pode fazer isso com uma visualização em linha.

Mais informações podem ser encontradas aqui e aqui .

Você precisa ter cuidado com os CTEs no SQL server não apenas no oracle, há casos em que as consultas têm um desempenho muito pior ao usar CTEs em comparação com subconsultas, cross apply, etc.

Como sempre, é importante testar qualquer consulta sob várias condições de carga para determinar qual delas funciona melhor.

Semelhante ao @scsimon com oracle, às vezes o servidor MS SQL não faz o que você espera em relação ao uso do índice.

Se você for usar os mesmos dados mais de uma vez, os CTEs podem ser mais úteis, se você estiver usando apenas uma vez, geralmente uma subconsulta é mais rápida em grandes conjuntos de dados.

por exemplo, selecione * de (minha subconsulta) junte-se a outra coisa ...