Joining multiple SQL queries into a single query

Question

Joining multiple SQL queries into a single query

Navigation

#1 by (6 votes)

2

I have a 'mission' in my work, where I need to join some queries in a single query. But I have no idea how I can do this so that everything I need is returned with just one query to the bank. Is there any way to merge 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 more 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.

sql postgresql

asked by anonymous 03.07.2014 / 15:10

1 answer

How to click and descend a hidden part? Block login through CURL PHP

score 6 · Accepted Answer

"Naive" solution

You can use UNION as long as you keep the fields the same, 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

Is this the best solution?

No, it's a gambiarra. Running 3 queries on one does not leave the bank connection faster. In fact the bank will run the 3 queries normally and may still have an overhead of memory.

What often leaves many queries running slow is not correctly using the statements objects and connections to the database. I worked on a Java system that removed connections from the pool and closed them for each query. We changed the system to recover a single connection per request, and the performance gain was 300%.

Even though there is some minimum gain from 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 in cache . Could not this% recovered% be stored in memory? If changes are not frequent this will bring a good performance gain. You just can not forget to invalidate the cache when the table or registry changes.

Alternative for query within loop

For queries executed within a loop , you can use caching techniques in the code in question.

I'll put a fictitious example, but illustrate 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) to be searched for in advance, just put the queries that repeat outside 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 the tables or put in a subquery .

First of all, I did not understand why you can not recover tag already in the first query .

In addition, I do not know what the seq relationship is with the others, but if you have any you could do a subquery like this:

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