IF / ELSE LEFT JOIN

Question

I need to do LEFT JOIN with IF / ELSE, this is my request:

IF (M.idArtVar=null, LEFT JOIN ArtMaga G ON (G.idMagazzino = V.idMagazzino AND G.idArticolo = M.idArticolo), LEFT JOIN ArtMaga G ON (G.idMagazzino = V.idMagazzino AND G.idArticolo = M.idArticolo AND G.idArtVar = M.idArtVar) ) 

But that will not work.

I also tried like this:

  LEFT JOIN ArtMaga AM ON IF(M.idArtVar IS NULL, (AM.idMagazzino = TM.idMagazzino AND AM.idArticolo = A.idArticoli), (AM.idMagazzino = TM.idMagazzino AND AM.idArtVar = M.idArtVar)) 

But this request is too slow. How can i do Thanks.

EDIT: this is the complete request:

  SELECT F.Codice AS "CodiceFornitore", F.RagioneSociale AS "RagioneSocialeFornitore", A.ArticoloFornitore, C.Descrizione AS CatDes, S.Descrizione AS Settore, U.Sigla AS Um, U2.Sigla AS Um2, A.Moltiplicatore AS Molt, A.Collo, TM. dMagazzino, M.idArtVar, AM.Esistenza, AM.Disponibilita, AM.QtaImpegnata, AM.QtaOrdinata, TM.TipoSoggetto, TM.idSoggetto, ST.DataMovimento, MC.Codice, ST.Quantita, ST.Prezzo, ST.Sconti, M.idMagaRigMov FROM MagaRigMov M LEFT JOIN Articoli A ON A.idArticoli = M.idArticolo LEFT JOIN UnMisura U ON U.idUnMisura = A.idUnMisura1 LEFT JOIN UnMisura U2 ON U2.idUnMisura = A.idUnMisura2 LEFT JOIN Iva I ON I.idIva = A.idIva LEFT JOIN Settori S ON S.idSettori = A.idSettore LEFT JOIN Fornitori F ON F.idFornitori = A.idFornitore LEFT JOIN ArtCategorie C ON C.idArtCategorie = A.idArtCategoria LEFT JOIN MagaTesMov TM ON TM.idMagaTesMov = M.idMagaTesMov LEFT JOIN STORICO ST ON (ST.idSoggetto = TM.idSoggetto AND ST.TipoSoggetto = TM.TipoSoggetto AND ST.idArticolo = M.idArticolo) LEFT JOIN MagaCausali MC ON MC.idMagaCausali = ST.idMagaCausale LEFT JOIN ArtMaga AM ON IF(M.idArtVar IS NULL,(AM.idMagazzino = TM.idMagazzino AND AM.idArticolo = A.idArticoli), (AM.idMagazzino = TM.idMagazzino AND AM.idArtVar = M.idArtVar)) 

This request is too slow, but it works.

Answer 1

You cannot use IF to create a conditional join. Since IF not part of the SELECT syntax, and even if it was (for example, CASE expressions), this will not be allowed to be used that way. You can move the logic to the ON statement, though:

 LEFT JOIN ArtMaga G ON (G.idMagazzino = V.idMagazzino AND G.idArticolo = M.idArticolo) AND M.idArtVar IS NULL OR (G.idMagazzino = V.idMagazzino AND G.idArticolo = M.idArticolo AND G.idArtVar = M.idArtVar) AND M.idArtVar IS NOT NULL 

which can be simplified to:

 LEFT JOIN ArtMaga G ON (G.idMagazzino = V.idMagazzino AND G.idArticolo = M.idArticolo) AND (M.idArtVar IS NULL OR G.idArtVar = M.idArtVar) 

Also note that you cannot use equality to check if an expression is null.
M.idArtVar = null will never be true, because NULL never be anything (even before NULL ). A way to check if an expression is null using IS NULL .

---

In your second query, these words use the MySQL IF() function and seem to be correct (although I see a difference in the code with the first query, the condition G.idArticolo = M.idArticolo been removed from one part.)

Why the request is slow depends on many factors, and using functions in a join environment can be one of many. Try the changes I suggest above. If it is still slow, you will need to examine the execution plan and the available indexes in the tables.

Answer 2

Just put the condition in the on clause. If not part of the SQL statement.

 SELECT F.Codice AS "CodiceFornitore", F.RagioneSociale AS "RagioneSocialeFornitore", A.ArticoloFornitore, C.Descrizione AS CatDes, S.Descrizione AS Settore, U.Sigla AS Um, U2.Sigla AS Um2, A.Moltiplicatore AS Molt, A.Collo, TM. dMagazzino, M.idArtVar, AM.Esistenza, AM.Disponibilita, AM.QtaImpegnata, AM.QtaOrdinata, TM.TipoSoggetto, TM.idSoggetto, ST.DataMovimento, MC.Codice, ST.Quantita, ST.Prezzo, ST.Sconti, M.idMagaRigMov FROM MagaRigMov M LEFT JOIN Articoli A ON A.idArticoli = M.idArticolo LEFT JOIN UnMisura U ON U.idUnMisura = A.idUnMisura1 LEFT JOIN UnMisura U2 ON U2.idUnMisura = A.idUnMisura2 LEFT JOIN Iva I ON I.idIva = A.idIva LEFT JOIN Settori S ON S.idSettori = A.idSettore LEFT JOIN Fornitori F ON F.idFornitori = A.idFornitore LEFT JOIN ArtCategorie C ON C.idArtCategorie = A.idArtCategoria LEFT JOIN MagaTesMov TM ON TM.idMagaTesMov = M.idMagaTesMov LEFT JOIN STORICO ST ON (ST.idSoggetto = TM.idSoggetto AND ST.TipoSoggetto = TM.TipoSoggetto AND ST.idArticolo = M.idArticolo) LEFT JOIN MagaCausali MC ON MC.idMagaCausali = ST.idMagaCausale LEFT JOIN ArtMaga AM ON (AM.idMagazzino = TM.idMagazzino AND (m.idartVar is NULL and AM.idArtVar = M.idArtVar or AM.idArticolo = A.idArticoli))