Should I use a Select with Case or If else, union in this Inner Join statement?

Question

How do I perform an SQL SELECT statement which returns data from clients according to their type, BUT depending on the type, one query has more columns than other, thus i can't do an UNION command, even though puting one more null column to the statement as they would have the same number of columns.

the key fields P.PesTpP 'Tipo' is the type of client, which can will return

I tried to do something like this but it returns this error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

select P.PesCod 'Codigo', P.PesNom 'Nome Cliente', P.PesNomRes 'Nome Resumido', P.PesEMl 'E-mail', pt.pestel 'Telefone', e.PesEnd 'Endereco', e.PesEndNum 'Numero', e.PesEndCmp 'Logradouro', e.PesBai 'Bairro', e.PesCep 'CEP', e.PesCidCod 'IBGE', P.PesTpP 'Tipo',
j.jurCod 'CNPJ'

from PesCad P, cptcli c, PESTEL pt, PesEnd e,
PESJUR J
where P.PesCod = c.CliCod and P.PesCod = pt.PesCod and P.PesCod = e.PesCod and p.PesCod = j.JurCod and P.PesTpP = 'J'

UNION ALL

select P.PesCod 'Codigo', P.PesNom 'Nome Cliente', P.PesNomRes 'Nome Resumido', P.PesEMl 'E-mail', pt.pestel 'Telefone', e.PesEnd 'Endereco', e.PesEndNum 'Numero', e.PesEndCmp 'Logradouro', e.PesBai 'Bairro', e.PesCep 'CEP', e.PesCidCod 'IBGE', P.PesTpP 'Tipo', F.FisRGNum 'RG', f.FisCPF'CPF'

from PesCad P, cptcli c, PESTEL pt, PesEnd e,
PESFIS F

where P.PesCod = c.CliCod and P.PesCod = pt.PesCod and P.PesCod = e.PesCod and p.PesCod = F.FisCod and P.PesTpP = 'F'

Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh, Dec 17 '19 at 14:09
Make it easy to assist you, show us some sample table data and the expected result (as formatted text, no images.) Simplify! [mcve] — jarlh, Dec 17 '19 at 14:10
Why can't you put `NULL` columns for the `SELECT` that returns fewer columns? — Parfait, Dec 17 '19 at 14:55

score 0 · Answer 1 · answered Dec 17 '19 at 14:22

Given your query , I don't think it is necessary to use a UNION here - you could use two left join to get all the desired information in one pass:

select  p.PesCod 'Codigo',
        p.PesNom 'Nome Cliente',
        p.PesNomRes 'Nome Resumido',
        p.PesEMl 'E-mail',
        pt.pestel 'Telefone',
        e.PesEnd 'Endereco',
        e.PesEndNum 'Numero',
        e.PesEndCmp 'Logradouro',
        e.PesBai 'Bairro',
        e.PesCep 'CEP',
        e.PesCidCod 'IBGE',
        P.PesTpP 'Tipo',
        j.jurCod 'CNPJ',
        f.FisRGNum 'RG',
        f.FisCPF 'CPF'
from    PesCad p join cptcli c on P.PesCod = c.CliCod
        join PESTEL pt on P.PesCod = pt.PesCod
        join PesEnd e on P.PesCod = e.PesCod
        left join PESJUR j on p.PesCod = j.JurCod and P.PesTpP = 'J'
        left join PESFIS f on p.PesCod = F.FisCod and P.PesTpP = 'F'

Columns from PESJUR will be null when PesTpP is not J and columns from PESFIS will be null when PesTpP is not F

As suggested in the comments, I have also updated your join syntax to the explicit syntax, which a lot of people find easier to understand

score 0 · Answer 2 · answered Dec 17 '19 at 15:32

Consider best practices of standard ANSI SQL:

Explicit JOIN for multiple table relations instead of implicit joins using WHERE. Even though both return equivalent performance, the explicit join is arguably more readable and maintainable;
Optional AS keyword for column or table aliases for clarity in renaming original identifiers during the execution of query. Arguably, AS is more readable in SELECT clause but some IDEs/CLIs or JDBC/ODBC drivers may require the keyword and for table aliases in FROM and JOIN clauses.
Double quotes for column aliases instead of single quotes (which is not ANSI and can fail in some RDBMS's). Even double quotes are optional and only needed if you want to preserve upper/lower case combination, use special characters (e.g., space, hyphen), and keywords. Remember the old axiom:

[S]ingle quotes are for [S]trings ; [D]ouble quotes are for [D]atabase identifiers.

Therefore, should you still want to maintain the UNION query approach, simply add NULL or constant (e.g., 0,'N/A') columns for unmatched fields between the SELECT expressions:

SELECT p.PesCod AS "Codigo", p.PesNom AS "Nome Cliente", p.PesNomRes AS "Nome Resumido", 
       p.PesEMl AS "E-mail", pt.pestel AS "Telefone", e.PesEnd AS "Endereco", 
       e.PesEndNum AS "Numero", e.PesEndCmp AS "Logradouro", e.PesBai AS "Bairro",
       e.PesCep AS "CEP", e.PesCidCod AS "IBGE", p.PesTpP AS "Tipo", 
       j.jurCod AS "CNPJ", NULL AS "RG", NULL AS "CPF"           --- UNMATCHED COLUMNS

FROM PesCad p
INNER JOIN cptcli c ON p.PesCod = c.CliCod
INNER JOIN PESTEL pt ON p.PesCod = pt.PesCod
INNER JOIN PesEnd e ON p.PesCod = e.PesCod
INNER JOIN PESJUR j ON p.PesCod = j.JurCod
WHERE p.PesTpP = 'J'

UNION ALL

SELECT p.PesCod AS "Codigo", p.PesNom AS "Nome Cliente", p.PesNomRes AS "Nome Resumido", 
       p.PesEMl AS "E-mail", pt.pestel AS "Telefone", e.PesEnd AS "Endereco", 
       e.PesEndNum AS "Numero", e.PesEndCmp AS "Logradouro", e.PesBai AS "Bairro",
       e.PesCep AS "CEP", e.PesCidCod AS "IBGE", p.PesTpP AS "Tipo", 
       NULL AS "CNPJ", f.FisRGNum AS "RG", f.FisCPF AS "CPF"     --- UNMATCHED COLUMNS

FROM PesCad p 
INNER JOIN cptcli c ON p.PesCod = c.CliCod
INNER JOIN PESTEL pt ON p.PesCod = pt.PesCod
INNER JOIN PesEnd e ON p.PesCod = e.PesCod
INNER JOIN PESFIS f ON p.PesCod = F.FisCod
WHERE p.PesTpP = 'F'

Should I use a Select with Case or If else, union in this Inner Join statement?

2 Answers2