SQL - search data from different tables without relationship with Ids

Asked

Viewed 64 times

-1

Personal table (id, cnae, cnpj, ...) (amount of records 40M)
Table cnae_secundario (id, cnae, cnpj) has no attachment by id with the personal table. (amount of 50M records)

The user will inform the cnae and need to seek both the people that have this cnae, how many people Uridicas that have this cnae as secondary.

I arrived in this SQL but the query was very long.

Select * FROM 
(   
    SELECT pj.id, pj.cnpj
    FROM Pessoa_Juridica pj 
    WHERE 
    pj.cnae in ('6499999')
    
    UNION
    
    SELECT pj.id, pj.cnpj
    from cnae_secundario c
    join pessoa_juridica pj on pj.cnpj = c.cnpj
    where c.cnae in ('6499999')
    and pj.cnae not in ('6499999')
) as empresas
sql postgresql select inner-join

  • Hello @bob, I think a UNION ALL should solve your problem

  • Hi @Viniciusdutra I got to use UNION ALL tbem, but the problem is that this very time-consuming search, and has times q comes to give memory problems, I wanted to see if you have any solution q nor use UNION and such.

  • Important you [Dit] your question and explain objectively and punctually the difficulty found, accompanied by a [mcve] problem (including indexes, structures etc., who knows the explain of queries) and attempt to solution. To better enjoy the site, understand and avoid closures and negativities worth reading the Stack Overflow Survival Guide in English.

  • How the hell do you make an example with 40/50M records ?! Database is not always easy to reproduce the problem @Bacco , with due respect as they say in the STF...would improve the question ? It would , in this I agree.

  • 3

    @Motta a verifiable example does not need (nor should) have the 50M. Any basic example with half a dozen linhasa will already reveal structural flaws and problems with index, and already serves for an explain. If someone needs to go further, generating 50M locally should theoretically be a basic requirement for anyone who wants to give a better elaborate answer (even so, it would be an extra).

  • @Bacco very topical has really low quality (or none) but to moderators I ask them to reflect before closing , it is not always easy decision.

  • 3

    @Motta this type of discussion is pro site [meta]. Regarding the specific case, zero difficulty, it is a clear situation of closure (the answer only serves to the author, which attests that the closure was correct, after all we are not a help site, nor Helpdesk, but a knowledge reposition for a broad audience). I’m not against helping, of course, but keeping it long-term is obviously not the function of the site. As it stands, the author’s problem has been solved. If it were edited as directed, the energy invested might serve more people.

Show 2 more comments

1 answer

0


--try

SELECT pj.id, pj.cnpj
    FROM Pessoa_Juridica pj 
    WHERE  ( pj.cnae in ('6499999')
             or
             exists (SELECT null
                     from cnae_secundario c
                     where c.cnpj = pj.cnpj
                     and c.cnae in ('6499999')) )

  • thank you very much worked your solution, took a little while but was, Valew

Browser other questions tagged sql postgresql select inner-join

You are not signed in. Login or sign up in order to post.