Why SQL Server sql query returns empty list?

Question

Please note I am aware that it must be something simple and probably obvious - I just cannot see it ... As stated, do you know why this sql query would return empty list when executed on SQL Server (via SQL Server Management Studio), provided THERE IS DATA IN APPROPRIATE TABLES AND ALL THE PARAMETERS OF THE QUERY ARE SET CORRECTLY?

This question is linked to my other question (Does anyone know why JPQL query on a view with SQL Server does not work? 'GetResultList' always returns empty list) but at this stage it actually turns into a separate topic, so therefore I have placed it here. Here is the query:

SELECT t1.Id,
         t2.IdTransiti,
         t1.CodiceCaricoPericoloso,
         t1.Confidenza,
         t1.Istante,
         t1.IstanteRicezione,
         t2.SQLServer_latitude,
         t2.SQLServer_longitude,
         t1.Partizione,
         t1.Targa,
         t1.TargaSecondaria,
         t1.UtmcInstanceId,
         t1.VelocitaStimata,
         t1.IdColore,
         t1.IdCorsia,
         t1.IdMarca,
         t1.IdModello,
         t1.IdNazionalita,
         t1.IdSerie,
         t1.IdTipologiaVeicolo
    FROM dbo.TransitiView t1
    LEFT OUTER JOIN Serie t0
       ON (t0.Id = t1.IdSerie)
    LEFT OUTER JOIN dbo.Colori t3
       ON (t3.Id = t1.IdColore)
    LEFT OUTER JOIN Modelli t4
       ON (t4.Id = t1.IdModello)
    LEFT OUTER JOIN Marche t5
       ON (t5.Id = t1.IdMarca),
    TransitiPunti t2,
    Corsie t6
   WHERE ( ( ( ( ( (t6.Attiva = 1)
                  AND (t1.Istante BETWEEN '2015-09-03 00:16:50.693'
                                      AND '2015-09-03 23:16:50.693'))
                AND ( (t1.Partizione = 0) OR (t1.Partizione = 6)))
              AND (t1.IdCorsia = 1))
            AND (t2.IdTransiti = t1.Id))
          AND (t6.Id = t1.IdCorsia))
ORDER BY t1.Istante DESC

---

EDIT:

Thanks to russ answer I now know what is going on - SQL Server does not like empty records in inner joins - ODD!! Please, does any one of you know how and if it is possible to make SQL Server "LIKE" inner joins where entries may be missing in corresponding columns in joined tables? Or... At least how to make it work for this particular query?

I thought to make it: ...ON (t2.IdTransiti = t1.Id or t2.IdTransiti is null) but that is NOT good enough for SQL Server. it didn't help :(.

BONUS QUESTION: Any idea why it is ok for MySQL but not for SQL Server when tables joined via inner joins may be empty? Why should this be NOT ok in a first place?

---

EDIT:

ANSWER TO THE PROBLEM: Thanks to russ - I basically got it - it is just my confusion and nothing more. Please read comments under the accepted answer. Everything stems from the fact I forgot, didn't know that @SecondaryTable annotation in JPA translates to inner join and then double confusion - by my disregard THAT OBVIOUSLY INNER JOIN EXPECTS VALUES IN BOTH TABLES !!!

Thanks to everyone for their effort and I guess I have to think little bit more before stating things next time around :).

Answer 1

If you think the table [TransitiPunti] has no records in it then yes that would stop any query using an INNER JOIN on that query from producing rows. note: Your existing query does use an INNER JOIN on that table (it uses old fashioned syntax regretably, but it is an inner join).

I suggest you test "progressively" so that you can work out the problem. "Start small", try that, if successful add items one at a time and try again.

As soon as you stop getting results you know what the last item was that you added, and it will be that item that causes the problem.

Here for example are 3 queries to try.

/* one table only (t1) */

SELECT
      t1.Id
    , t1.CodiceCaricoPericoloso
    , t1.Confidenza
    , t1.Istante
    , t1.IstanteRicezione
    , t1.Partizione
    , t1.Targa
    , t1.TargaSecondaria
    , t1.UtmcInstanceId
    , t1.VelocitaStimata
    , t1.IdColore
    , t1.IdCorsia
    , t1.IdMarca
    , t1.IdModello
    , t1.IdNazionalita
    , t1.IdSerie
    , t1.IdTipologiaVeicolo
FROM dbo.TransitiView t1
WHERE t1.Istante BETWEEN '2015-09-03 00:16:50.693' AND '2015-09-03 23:16:50.693'
      AND (t1.Partizione = 0 OR t1.Partizione = 6)
      AND t1.IdCorsia = 1
ORDER BY t1.Istante DESC
;

/* two tables (t1 & t6) */

SELECT
      t1.Id
    , t1.CodiceCaricoPericoloso
    , t1.Confidenza
    , t1.Istante
    , t1.IstanteRicezione
    , t1.Partizione
    , t1.Targa
    , t1.TargaSecondaria
    , t1.UtmcInstanceId
    , t1.VelocitaStimata
    , t1.IdColore
    , t1.IdCorsia
    , t1.IdMarca
    , t1.IdModello
    , t1.IdNazionalita
    , t1.IdSerie
    , t1.IdTipologiaVeicolo
FROM dbo.TransitiView t1
      INNER JOIN Corsie t6 ON t6.Id = t1.IdCorsia
WHERE t6.Attiva = 1
      AND t1.Istante BETWEEN '2015-09-03 00:16:50.693' AND '2015-09-03 23:16:50.693'
      AND (t1.Partizione = 0 OR t1.Partizione = 6)
      AND t1.IdCorsia = 1
ORDER BY t1.Istante DESC
;

/* three tables t1, t2, t6 */

SELECT
      t1.Id
    , t2.IdTransiti
    , t1.CodiceCaricoPericoloso
    , t1.Confidenza
    , t1.Istante
    , t1.IstanteRicezione
    , t2.SQLServer_latitude
    , t2.SQLServer_longitude
    , t1.Partizione
    , t1.Targa
    , t1.TargaSecondaria
    , t1.UtmcInstanceId
    , t1.VelocitaStimata
    , t1.IdColore
    , t1.IdCorsia
    , t1.IdMarca
    , t1.IdModello
    , t1.IdNazionalita
    , t1.IdSerie
    , t1.IdTipologiaVeicolo
FROM dbo.TransitiView t1
      INNER JOIN TransitiPunti t2 ON t2.IdTransiti = t1.Id
      INNER JOIN Corsie t6 ON t6.Id = t1.IdCorsia
WHERE t6.Attiva = 1
      AND t1.Istante BETWEEN '2015-09-03 00:16:50.693' AND '2015-09-03 23:16:50.693'
      AND (t1.Partizione = 0 OR t1.Partizione = 6)
      AND t1.IdCorsia = 1
ORDER BY t1.Istante DESC

NEVER EVER use a mixture of old join syntax with the (better) explicit ANSI joins.

I also dislike redundant parentheses, I believe they confuse rather than aid.

SELECT
      t1.Id
    , t2.IdTransiti
    , t1.CodiceCaricoPericoloso
    , t1.Confidenza
    , t1.Istante
    , t1.IstanteRicezione
    , t2.SQLServer_latitude
    , t2.SQLServer_longitude
    , t1.Partizione
    , t1.Targa
    , t1.TargaSecondaria
    , t1.UtmcInstanceId
    , t1.VelocitaStimata
    , t1.IdColore
    , t1.IdCorsia
    , t1.IdMarca
    , t1.IdModello
    , t1.IdNazionalita
    , t1.IdSerie
    , t1.IdTipologiaVeicolo
FROM dbo.TransitiView t1
      /*
      LEFT OUTER JOIN Serie t0 ON t0.Id = t1.IdSerie
      LEFT OUTER JOIN dbo.Colori t3 ON t3.Id = t1.IdColore
      LEFT OUTER JOIN Modelli t4 ON t4.Id = t1.IdModello
      LEFT OUTER JOIN Marche t5 ON t5.Id = t1.IdMarca
      */
      INNER JOIN TransitiPunti t2 ON t2.IdTransiti = t1.Id
      INNER JOIN Corsie t6 ON t6.Id = t1.IdCorsia
WHERE t6.Attiva = 1
      AND t1.Istante BETWEEN '2015-09-03 00:16:50.693' AND '2015-09-03 23:16:50.693'
      AND (t1.Partizione = 0 OR t1.Partizione = 6)
      AND t1.IdCorsia = 1
ORDER BY t1.Istante DESC

In taking out the parentheses and correcting for the join syntax I think the query looks like the above. Note there is no apparent need for any of the left joined tables.

by the way this:

AND (t1.Partizione = 0 OR t1.Partizione = 6)

could be changed to:

AND t1.Partizione IN (0,6)