Subquery no SELECT

Olá Marcelo,

O resultado da subquery será sim uma coluna, como se fosse uma coluna retornada do seu "FROM paginas p1".

Seu uso nesta query é obter um valor agrupado (MAX(tamanho_pagina)), o qual não pode ser obtido através da query original, uma vez que não está sendo realizado agrupamento na query.

A subquery neste exemplo foi utilizada para obter o maior valor da coluna "tamanho_arquivo" para aquele site_id. Para isto, a subquery identifica qual seria o "relacionamento" entre os registros retornados da tabela paginas p1 e a sua subquery cuja tabela é paginas p2.

Para facilitar o entendimento e partindo da sua query que possui somente uma tabela, considere que tem a seguinte tabelas paginas, compostas pelas colunas site_id, arquivo_id, nome_site, nome_arquivo e tamanho_arquivo:

Tabela paginas:
site_id  arquivo_id  nome_site  nome_arquivo  tamanho_arquivo
1        1           UOL        index.htm     10
1        2           UOL        consulta.php  15
1        3           UOL        pag1.htm      3
2        1           Terra      default.asp   7
2        2           Terra      login.asp     30

Se você executar somente a consulta a seguir, obterá os seguintes dados:

SELECT p1.site_id, 
    p1.nome_site
FROM paginas p1;

Retorno:
site_id  nome_site  
1        UOL        
1        UOL        
1        UOL        
2        Terra      
2        Terra      

Entretanto, ao adicionar a subquery, ele irá buscar o valor máximo de tamanho_pagina para cada site_id de p1 - Ilustrando:

SELECT p1.site_id, p1.nome_site,
  (SELECT MAX(tamanho_arquivo)
   FROM paginas p2
   WHERE p1.site_id = p2.site_id) subquery2
FROM paginas p1;

site_id  nome_site  "Acao da subquery"
1        UOL         "SELECT MAX(tamanho_arquivo) FROM paginas p2 WHERE site_id=1"
1        UOL         "SELECT MAX(tamanho_arquivo) FROM paginas p2 WHERE site_id=1"        
1        UOL         "SELECT MAX(tamanho_arquivo) FROM paginas p2 WHERE site_id=1"
2        Terra       "SELECT MAX(tamanho_arquivo) FROM paginas p2 WHERE site_id=2"
2        Terra       "SELECT MAX(tamanho_arquivo) FROM paginas p2 WHERE site_id=2"

Obtendo o resultado:
site_id  nome_site  MAX(tamanho_arquivo)
1        UOL        15
1        UOL        15
1        UOL        15
2        Terra      30
2        Terra      30

Espero ter ajudado.

Não entendi o porquê do uso

WHERE p1.site_id = p2.site_id

Se a tabela é a mesma pq comparar o campo 'site_id'?

O motivo da comparação de p1.site_id = p2.site_id é que apesar de estar trabalhando com a mesma tabela, você está criando dois subconjuntos de dados distintos. Em P1, você está trabalhando com a tabela paginas inteira, já em P2, você está trabalhando somente com duas colunas, sendo uma o valor máximo de tamanho_pagina e o site_id.

Entretanto, para fazer a junção corretamente dos dados, é necessário fazer a comparação p1.site_id = p2.site_id, para indicar a qual site_id do subconjunto p1, pertence quele max(tamanho_pagina). Caso contrário, ele faria um plano cartesiano, gerando todas as combinacões possíveis. Ex:

site_id  nome_site  MAX(tamanho_arquivo)
1        UOL        15
1        UOL        15
1        UOL        15
2        Terra      15
2        Terra      15
1        UOL        30
1        UOL        30
1        UOL        30
2        Terra      30
2        Terra      30