SQL use of LEFT JOIN with OR

Asked

Viewed 1,512 times

1

I’m new to the SQL and I have the following question concerning LEFT JOIN: I have to list a table of cliente where each customer has a id and a code client with another table containing the addresses; in some cases the client has id, in others, he possesses the code.

I tried using the query below, but I do not know if it is the best choice or if it is correct.

SELECT cli.clicod, cli.atlcod, clinompessoa, grade.camnum 
FROM cli
LEFT JOIN codEnd on cli.clicod = codEnd.clicod 
LEFT JOIN grade on cli.clicod = grade.clicod or cli.atlcod = grade.atlcod
WHERE logcod = 32388
  AND camnum = 520

summarized tables basically I want to get customers belonging to the streets that have the campaign 520, spreadsheets are old without the possibility of normalizing the data

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

sql sql-server

  • Matthew, to be able to help you would need to post the structure of the tables that are being used in your SQL statement

  • @Mateussilva LEFT JOIN is used when the junction is not required.

  • 1

    As far as I can see, the code is correct. The LEFT JOIN should be used when there is no match in the second table. If there is always a match, we can use the INNER JOIN.

  • Will LEFT JOIN is the ideal? understanding that it will align the left columns always and if there are then the right ones, if you want to mandatory in the result, it would no longer be appropriate a INNER JOIN? Anyway, does your query return what you want? If yes then the question would refer to the optimization of this query and not if it is correct, I suggest doing some tests in ideal and unusual situations to check if everything goes well.

  • Apparently, you are correct. What it has to do is: will the relationship of "cli" with "codEnd" not always occur, if this always occurs the recommended is to use the "INNER JOIN". About LEFT JOIN with "or" you can take a test and measure performance, develop 2 queries with the conditions you put in the relationship of the table "grid" using INNER JOIN. In this case, you would use UNION to relate your 2 queries. The other possible simpler solution, using "IN" do not recommend, is usually slower than using "LEFT JOIN".

  • I understood that the OP is wanting to OR use the code when there is OR use the id as the Join key if the code does not exist. That’s what you expect @Matthew?

  • Tell me what I want

  • basically are 3 old tables

Show 3 more comments

1 answer

1

Depending on the purpose, the consultation may have LEFT JOIN or INNER JOIN.

If what you want is to get the results by directly linking the 3 tables, where a table record cli exists obligatorily in 1 or more records in the table codEnd and grade, then you should use the INNER JOIN with a OR at the junction, so as to connect by one column or another:

SELECT      C.clicod
        ,   C.atlcod
        ,   C.clinompessoa
        ,   G.camnum 
FROM        cli     C
INNER JOIN  codEnd  CE  ON  C.clicod = CE.clicod
                        OR  C.atlcod = CE.atlcod
INNER JOIN  grade   G   ON  C.clicod = G.clicod 
                        OR  C.atlcod = G.atlcod
WHERE       logcod = 32388
        AND camnum = 520

If you want results from the table cli, but does not know if there is a direct link or simply the table cli has a relationship of 0:N (0 to N) or 0:1 (0 to 1) with the other tables, then use the LEFT JOIN to avoid unduly restricting consultation:

SELECT      C.clicod
        ,   C.atlcod
        ,   C.clinompessoa
        ,   G.camnum 
FROM        cli     C
LEFT JOIN   codEnd  CE  ON  C.clicod = CE.clicod
                        OR  C.atlcod = CE.atlcod
LEFT JOIN   grade   G   ON  C.clicod = G.clicod 
                        OR  C.atlcod = G.atlcod
WHERE       logcod = 32388
        AND camnum = 520

This second option is safer when we are not sure what is in the related tables.

Heed that in both cases we may have duplicates due to the JOIN between several tables, especially when the relationship is 0:N or 1:N.

Browser other questions tagged sql sql-server

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