TÓPICO 7 – USANDO SUBQUERIES

No tópico 7, falarei um pouco sobre subqueries, como e quando usa-las.

1) QUANDO USAR SUBQUERIES:

As subqueries podem resolver os seguintes problemas:

  • Criando tabelas populadas: Uma subquery pode ser usada em um comando CREATE TABLE para criar e já popular uma tabela, técnica essa chamada de CTAS ou CREATE TABLE AS SELECT. Sua sintaxe é:
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIFRBQkxFIE1JTkhBX1RBQkVMQV9DT1BJQSAKICAgIEFTIFNFTEVDVCAqIEZST00gTUlOSEFfVEFCRUxBX09SSUdJTkFM” hover_enabled=”0″]Q1JFQVRFIFRBQkxFIE1JTkhBX1RBQkVMQV9DT1BJQSAKICAgIEFTIFNFTEVDVCAqIEZST00gTUlOSEFfVEFCRUxBX09SSUdJTkFM[/et_pb_dmb_code_snippet]
  • Manipulação de grandes quantidades de dados: Subqueries podem ser incorporadas em comandos de INSERT e UPDATE para mover grandes quantidades de dados, ou alterar grandes quantidades de dados em um único SQL.
  • Definição dinâmica de VIEWs (Cláusula WITH)
  • Definição dinâmica de expressões com subqueries escalares (subqueries que retornam apenas um valor)

2) COMO USAR SUBQUERIES

Existem alguns tipos de subqueries, cada uma com suas vantagens/desvantagens. Abaixo é possível ver os tipos de subqueries bem como alguns exemplos para ajudar a entende-las.

NOTA.01 – Caso a subquery retorne 0 linhas, então o valor retornado pela subquery é NULL

NOTA.02 – É possível ter subqueries no WHERE, HAVING e GROUP BY.

2.1) SINGLE-ROW SUBQUERIES

Subqueries que retornam apenas uma linha, e uma ou varias colunas Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcmV0b3JuYSBvIGlkIGUgbyBub21lIGRvIGRlcGFydGFtZW50byBkbyBlbXByZWdhZG8gY2hhbWFkbyAKLS0gJ3N0YWxtYW4gam9uZXMnCi0tIGNhc28gYSBzdWJxdWVyeSByZXRvcm5lIG1haXMgZGUgdW1hIGxpbmhhLCBvIHNlZ3VpbnRlIGVycm8gCi0tIHNlcsOhIGxhbsOnYWRvOgotLSBPUkEtMDE0Mjc6IHNpbmdsZS1yb3cgc3VicXVlcnkgcmV0dXJucyBtb3JlIHRoYW4gb25lIHJvdwpTRUxFQ1QgSURfREVQQVJUQU1FTlRPLCBOT01FX2RFUEFSVEFNRU5UTyAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIAogV0hFUkUgSURfR0VSRU5URSA9IChTRUxFQ1QgSURfRU1QIAogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgV0hFUkUgTk9NRV9FTVAgPSAnU1RBTE1BTiBKT05FUycp” hover_enabled=”0″]LS0gcmV0b3JuYSBvIGlkIGUgbyBub21lIGRvIGRlcGFydGFtZW50byBkbyBlbXByZWdhZG8gY2hhbWFkbyAKLS0gJ3N0YWxtYW4gam9uZXMnCi0tIGNhc28gYSBzdWJxdWVyeSByZXRvcm5lIG1haXMgZGUgdW1hIGxpbmhhLCBvIHNlZ3VpbnRlIGVycm8gCi0tIHNlcsOhIGxhbsOnYWRvOgotLSBPUkEtMDE0Mjc6IHNpbmdsZS1yb3cgc3VicXVlcnkgcmV0dXJucyBtb3JlIHRoYW4gb25lIHJvdwpTRUxFQ1QgSURfREVQQVJUQU1FTlRPLCBOT01FX2RFUEFSVEFNRU5UTyAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIAogV0hFUkUgSURfR0VSRU5URSA9IChTRUxFQ1QgSURfRU1QIAogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgV0hFUkUgTk9NRV9FTVAgPSAnU1RBTE1BTiBKT05FUycp[/et_pb_dmb_code_snippet]

2.2) MULTIPLE-ROW SUBQUERIES

Subqueries que retornam 0, uma ou varias linhas. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcmV0b3JuYSBhbGd1bWFzIGluZm9ybWHDp8O1ZXMgc29icmUgb3MgZW1wcmVnYWRvcyBxdWUgcGVydGVuY2VtCi0tIGFvIHNldG9yICd0ZWNub2xvZ2lhIGRhIGluZm9ybWHDp8OjbycKLS0gcmVwYXJlbSBubyB1c28gZG8gbiwgcXVhbmRvIHNlIGVzcGVyYXIgcXVlIGEgc3VicXVlcnkgcmV0b3JuZSAKLS0gbWFpcyBkZSB1bWEgbGluaGEuClNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIFNBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gSU4gKFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8gCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfREVQQVJUQU1FTlRPUyAKIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJyk=” hover_enabled=”0″]LS0gcmV0b3JuYSBhbGd1bWFzIGluZm9ybWHDp8O1ZXMgc29icmUgb3MgZW1wcmVnYWRvcyBxdWUgcGVydGVuY2VtCi0tIGFvIHNldG9yICd0ZWNub2xvZ2lhIGRhIGluZm9ybWHDp8OjbycKLS0gcmVwYXJlbSBubyB1c28gZG8gbiwgcXVhbmRvIHNlIGVzcGVyYXIgcXVlIGEgc3VicXVlcnkgcmV0b3JuZSAKLS0gbWFpcyBkZSB1bWEgbGluaGEuClNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIFNBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gSU4gKFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8gCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfREVQQVJUQU1FTlRPUyAKIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJyk=[/et_pb_dmb_code_snippet]

2.3) MULTIPLE-COLUMN SUBQUERIES

Retorna mais de uma coluna para a query pai. Pode retornar uma ou várias linhas. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gcmV0b3JuYSBpbmZvcm1hw6fDtWVzIHNvYnJlIG8gZ2VyZW50ZSBkYSDDoXJlYSAKLS0gZGUgJ3RlY25vbG9naWEgZGEgaW5mb3JtYcOnw6NvJwotLSByZXBhcmVtIG5vIHVzbyBkZSAyIGNvbHVuYXMgYW50ZXMgZG8gb3BlcmFkb3IgaW4uIGVzc2FzIAotLSAyIGNvbHVuYXMgZGV2ZW0gc2VyIGVxdWl2YWxlbnRlcyBhcyBjb2x1bmFzIHVzYWRhcyBuYSBzdWJxdWVyeQoKU0VMRUNUIElEX0VNUCwKIE5PTUVfRU1QLAogU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIFdIRVJFIChJRF9ERVBBUlRBTUVOVE8sIElEX0VNUCkgCklOIChTRUxFQ1QgSURfREVQQVJUQU1FTlRPLCAKIElEX0dFUkVOVEUgCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfREVQQVJUQU1FTlRPUyAKIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJyk=” hover_enabled=”0″]LS0gcmV0b3JuYSBpbmZvcm1hw6fDtWVzIHNvYnJlIG8gZ2VyZW50ZSBkYSDDoXJlYSAKLS0gZGUgJ3RlY25vbG9naWEgZGEgaW5mb3JtYcOnw6NvJwotLSByZXBhcmVtIG5vIHVzbyBkZSAyIGNvbHVuYXMgYW50ZXMgZG8gb3BlcmFkb3IgaW4uIGVzc2FzIAotLSAyIGNvbHVuYXMgZGV2ZW0gc2VyIGVxdWl2YWxlbnRlcyBhcyBjb2x1bmFzIHVzYWRhcyBuYSBzdWJxdWVyeQoKU0VMRUNUIElEX0VNUCwKIE5PTUVfRU1QLAogU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIFdIRVJFIChJRF9ERVBBUlRBTUVOVE8sIElEX0VNUCkgCklOIChTRUxFQ1QgSURfREVQQVJUQU1FTlRPLCAKIElEX0dFUkVOVEUgCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfREVQQVJUQU1FTlRPUyAKIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJyk=[/et_pb_dmb_code_snippet]

2.4) CORRELATED SUBQUERIES:

Especifica colunas que pertencem a tabelas que são referenciadas pela tabela pai. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gbW9zdHJhIGluZm9ybWHDp8O1ZXMgZG9zIGdlcmVudGVzIGRlIGNhZGEgZGVwYXJ0YW1lbnRvCgpTRUxFQ1QgVEUuSURfRU1QLAogVEUuTk9NRV9FTVAsCiBURS5TQUxBUklPLAogVEUuSURfREVQQVJUQU1FTlRPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyBURQogV0hFUkUgVEUuSURfRU1QIElOIChTRUxFQ1QgVEQuSURfR0VSRU5URSAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFREIAogV0hFUkUgVEQuSURfREVQQVJUQU1FTlRPID0gVEUuSURfREVQQVJUQU1FTlRPKQoKLS0gdGFtYsOpbSDDqSBwb3Nzw612ZWwgc2VyIHVzYWRhIGVtIHVwZGF0ZXMKLS0gYWJhaXhvIHNlIGVzdGEgYXR1YWxpemFuZG8gb3Mgc2Fsw6FyaW9zIGRvcyBnZXJlbnRlcyBlbSAxMCUKClVQREFURSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgVEUKIFNFVCBTQUxBUklPID0gKFNBTEFSSU8gKiAxLjEwKQogV0hFUkUgSURfRU1QIElOIAogICAoU0VMRUNUIFRELklEX0dFUkVOVEUgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0RFUEFSVEFNRU5UT1MgVEQgCiAgICAgV0hFUkUgVEQuSURfREVQQVJUQU1FTlRPID0gVEUuSURfREVQQVJUQU1FTlRPKQ==” hover_enabled=”0″]LS0gbW9zdHJhIGluZm9ybWHDp8O1ZXMgZG9zIGdlcmVudGVzIGRlIGNhZGEgZGVwYXJ0YW1lbnRvCgpTRUxFQ1QgVEUuSURfRU1QLAogVEUuTk9NRV9FTVAsCiBURS5TQUxBUklPLAogVEUuSURfREVQQVJUQU1FTlRPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyBURQogV0hFUkUgVEUuSURfRU1QIElOIChTRUxFQ1QgVEQuSURfR0VSRU5URSAKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFREIAogV0hFUkUgVEQuSURfREVQQVJUQU1FTlRPID0gVEUuSURfREVQQVJUQU1FTlRPKQoKLS0gdGFtYsOpbSDDqSBwb3Nzw612ZWwgc2VyIHVzYWRhIGVtIHVwZGF0ZXMKLS0gYWJhaXhvIHNlIGVzdGEgYXR1YWxpemFuZG8gb3Mgc2Fsw6FyaW9zIGRvcyBnZXJlbnRlcyBlbSAxMCUKClVQREFURSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgVEUKIFNFVCBTQUxBUklPID0gKFNBTEFSSU8gKiAxLjEwKQogV0hFUkUgSURfRU1QIElOIAogICAoU0VMRUNUIFRELklEX0dFUkVOVEUgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0RFUEFSVEFNRU5UT1MgVEQgCiAgICAgV0hFUkUgVEQuSURfREVQQVJUQU1FTlRPID0gVEUuSURfREVQQVJUQU1FTlRPKQ==[/et_pb_dmb_code_snippet]

3) USANDO O EXISTS

O EXISTS testa a existência de uma linha em uma subquery. Caso nenhuma linha seja encontrada, retorna FALSE. Caso a subquery retorne mais de uma linha, se essa linha “bater” com a linha da query-pai o comando é encerrado, não sendo necessário todas as linhas do EXISTS serem validadas. Mais performático do que o operador IN. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gY2hlY2EgYSBleGlzdGVuY2lhIGRvIGdlcmVudGUgZG8gc2V0b3IgMQoKU0VMRUNUIFRFLk5PTUVfRU1QLAogVEUuU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgVEUKIFdIRVJFIEVYSVNUUyAoU0VMRUNUIDEKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFRECiBXSEVSRSBURC5JRF9HRVJFTlRFID0gVEUuSURfRU1QCiBBTkQgVEQuSURfREVQQVJUQU1FTlRPID0gMSk=” hover_enabled=”0″]LS0gY2hlY2EgYSBleGlzdGVuY2lhIGRvIGdlcmVudGUgZG8gc2V0b3IgMQoKU0VMRUNUIFRFLk5PTUVfRU1QLAogVEUuU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgVEUKIFdIRVJFIEVYSVNUUyAoU0VMRUNUIDEKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFRECiBXSEVSRSBURC5JRF9HRVJFTlRFID0gVEUuSURfRU1QCiBBTkQgVEQuSURfREVQQVJUQU1FTlRPID0gMSk=[/et_pb_dmb_code_snippet]

4) USANDO O WITH

O WITH determina nomes para subqueries (blocos de subqueries). Essa subquery, que foi nomeada é tratada como uma view dinâmica. Isso facilita a leitura e manutenção caso se tenha que trabalhar com muitas subqueries dentro de uma query Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gbW9zdHJhIG9zIGZ1bmNpb27DoXJpb3MgZG8gc2V0b3IgZGUgJ3RlY25vbG9naWEgZGEgaW5mb3JtYcOnw6NvJwoKV0lUSCBTRVRPUl9USSBBUyAoCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJwopClNFTEVDVCAqIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogV0hFUkUgSURfREVQQVJUQU1FTlRPID0gKFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8gRlJPTSBTRVRPUl9USSk=” hover_enabled=”0″]LS0gbW9zdHJhIG9zIGZ1bmNpb27DoXJpb3MgZG8gc2V0b3IgZGUgJ3RlY25vbG9naWEgZGEgaW5mb3JtYcOnw6NvJwoKV0lUSCBTRVRPUl9USSBBUyAoCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TIFdIRVJFIE5PTUVfREVQQVJUQU1FTlRPID0gJ1RFQ05PTE9HSUEgREEgSU5GT1JNQcOHw4NPJwopClNFTEVDVCAqIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogV0hFUkUgSURfREVQQVJUQU1FTlRPID0gKFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8gRlJPTSBTRVRPUl9USSk=[/et_pb_dmb_code_snippet]

Bem… é isso por enquanto pessoal. Até a próxima.

Keep Querying