Why is OR not good in a LEFT JOIN?

Asked

Viewed 72 times

3

I’m creating a query and my pull request returned because the Code Star considered that the OR is not good for relationship tables.

Example:

  FROM LEFT JOIN administracao.cidade cid
    ON (cid.cid_cd_cidade = cec.cec_cd_cidade)
    OR (cid.cid_cd_cidade = cli.cli_cd_cidade)

What would you recommend instead?

sql postgresql

  • I consult with or in junctions, in very specific cases. For example, if I can reach the same object through more than one path in the relationship. Like your case you put up, might come in cid through cec or of cli. He went deeper or was just achismo, the tester?

2 answers

1

Use the OR with LEFT JOIN, lets the query slow, an alternative is to use the UNION.

  FROM LEFT JOIN administracao.cidade cid
    ON (cid.cid_cd_cidade = cec.cec_cd_cidade)
  UNION
  FROM LEFT JOIN administracao.cidade cid
    On (cid.cid_cd_cidade = cli.cli_cd_cidade)

Take a test performing a query, and note the return time, and cite in the comments your experience.

  • I will carry out the test here, but there is another solution that I have been given here.

  • Put as answer too

  • I’m not a fan of UNIONs, I prefer UNION ALL.

0

I was able to solve a little bit, Ester did it this way:

  FROM LEFT JOIN administracao.cidade cid_cec
    ON cid_cec.cid_cd_cidade = cec.cec_cd_cidade
  LEFT JOIN administracao.cidade cid_cli
    ON cid_cli.cid_cd_cidade = cli.cli_cd_cidade

  • I must say that this consultation is potentially not the same as the alternative with the UNION, nor the OR. It turns out that here you add one more level in the Cartesian product. The query optimizer can handle this properly, but it would make his work more burdensome.

Browser other questions tagged sql postgresql

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