Uniting several SQL queries in a single query

Question

I have a 'mission' in my work, where I need to unite some queries in a single query. But I have no idea how I can do this so that everything I need is returned using just one query to the bank. Is there any way to join the queries? UNION does not solve the problem because the tables and data are different.

For example, merge this query:

SELECT b.natureza, COUNT(*) AS total
FROM tabela1 a
LEFT JOIN tabela2 b ON (a.servico = b.cod)
WHERE b.cod notnull AND
a.status = 0 AND a.data_mov BETWEEN 77770 AND 77810
Group by b.natureza
order by 1

... with this query:

SELECT tag FROM tabela3 WHERE cod = 1

... and with this:

SELECT seq 
FROM tabela1 a
LEFT JOIN tabela2 b ON (a.servico = b.cod)
WHERE b.cod NOTNULL AND b.natureza = QN:Nat AND 
a.status = 0 AND a.data_mov BETWEEN 77770 AND 77810
ORDER BY 1

so that all results are displayed in a single table.

Answer 1

Solution "naive"

You can use UNION as long as you keep the fields equal, leaving the unused ones empty.

Example:

SELECT b.natureza as natureza, COUNT(*) AS total, '' as tag, '' as seq
FROM tabela1 a
LEFT JOIN tabela2 b ON (a.servico = b.cod)
WHERE b.cod notnull AND
a.status = 0 AND a.data_mov BETWEEN 77770 AND 77810
Group by b.natureza
order by 1

UNION

SELECT '' as natureza, '' as total, tag, '' as seq FROM tabela3 WHERE cod = 1

UNION

SELECT '' as natureza, '' as total, '' as tag, seq 
FROM tabela1 a
LEFT JOIN tabela2 b ON (a.servico = b.cod)
WHERE b.cod NOTNULL AND b.natureza = QN:Nat AND 
a.status = 0 AND a.data_mov BETWEEN 77770 AND 77810
ORDER BY 1

This is the best solution?

No, it is a gambiarra. Running 3 queries in one does not make the connection to the bank faster. In fact the bank will run the 3 queries normally and may still have a overhead from memory.

What usually makes the execution of several queries slow is not using the objects of statements and connections to the database. I worked on a Java system that removed connections from pool and closed them for each query. We changed the system to recover a single connection per request and the performance gain was in the order of 300%.

Even if there is some minimum gain with this "union" in certain situations, for me this falls into the category of micro-optimization, that is, something very specific that solves a particular problem, but leaves the code more prone to errors and more difficult to maintain.

A more suitable solution that can usually be done is to put certain information on cache. That one tag recovered could not be stored in memory? If the changes are not frequent it will bring a good performance gain. You can only forget to invalidate the cache when the table or record is changed.

Alternative for within loop query

To darlings executed within a loop, it is possible to use caching techniques in the code in question.

I will put a fictitious example, but illustrating the principle:

mapaCache = { }
queryPrincipal = execute('select * from tabela_principal')
for (dados in queryPrincipal) {

    //recupera o código de algum lugar
    codigoTag = 1 

    //verifica se a tag já foi carregada
    tag = mapaCache[codigoTag]

    //se não tem ainda, carrega do banco e salva no cache
    if (tag == null) {
        tag = execute('select tag from tabela3')
        mapaCache[codigoTag] = tag
    }

    //coloca os dados no array

}

If you already know the code(s) (s) to be searched in advance, just put the repeating queries out of the loop. Example:

tag = execute('select tag from tabela3')
queryPrincipal = execute('select * from tabela_principal')
for (dados in queryPrincipal) {

    //coloca os dados no array

}

Join or Subquery

Another alternative would be to Join between tables or place in a subquery.

First of all, I don’t understand why you can’t recover seq already in the first query.

Besides, I don’t know what the relationship with tabela3 with the others, but if you have any you could do a subquery , thus:

SELECT b.natureza, COUNT(*) AS total, 
    (SELECT tag FROM tabela3 WHERE cod = tabela1.codigo_tag) as tag
FROM tabela1 a
LEFT JOIN tabela2 b ON (a.servico = b.cod)
WHERE b.cod notnull AND
a.status = 0 AND a.data_mov BETWEEN 77770 AND 77810
Group by b.natureza
order by 1