Different outcomes between Azure SQL and SQL Server Standard

Question

I have the same query in SQL Server Standard and Azure SQL (see below). But sadly they do not have the same outcome even though the data in the database is the same. Vraag (i.e. question) has 12 rows. One question is answered (userantwoord). On SQL Server it shows 12 questions in which one has id_antwoord. The others are Null. In Azure it only shows the question that is answered and leaves the rest out. When I only select questions with categories, it shows 12 questions.

SELECT vraag.i_d__categorie, vraag.i_d, vraag.niveau, vraag.omschrijving, 
       vraag.volgorde, vraag.code, vraag.gewicht, vraag.tekst, vraag.thema1, 
       vraag.thema2, vraag.thema3, vraag.thema4, vraag.thema5, vraag.thema6, 
       userantwoord.id_antwoord
FROM   categorie 
       LEFT OUTER JOIN vraag 
         LEFT OUTER JOIN Users 
           INNER JOIN userantwoord 
           ON Users.UserId = userantwoord.Id_user 
         ON vraag.i_d = userantwoord.id_vraag 
       ON categorie.i_d = vraag.i_d__categorie

WHERE categorie.link = @Onderwerp AND vraag.niveau = @Niveau 
      AND ([Users].UserId = @UserID OR [Users].UserId is null)
ORDER BY vraag.volgorde

Userantwoord is defined as follows:

CREATE TABLE [dbo].[userantwoord](
[id] [uniqueidentifier] NOT NULL,
[id_antwoord] [uniqueidentifier] NULL,
[id_vraag] [uniqueidentifier] NULL,
[Id_user] [uniqueidentifier] NULL,
CONSTRAINT [PK_userantwoord] PRIMARY KEY CLUSTERED

with foreign keys on all the right places. Is there something I forgot to look? I searched the internet for differences between Azure en SQL server but couldn't find anything so basic.

StuartLC StuartLC · Accepted Answer · 2015-11-14T14:39:41

Irrespective of the version and edition of Sql Server, I believe there is an underlying error in the original query. Seemingly, your requirement is to show the questions which have been answered by a user, for:

All Questions, for a given Category and Level
For a specific user, or for all users (depending on whether a null filter is provided)
If a user has not answered a particular question, there will be no corresponding row in userantwoord

I do not believe you should be joining through the userantwoord table with any kind of join, and instead, you can use CROSS JOIN to get the cartesian product of all questions for a given user or users, and then only LEFT JOIN to userantwoord which will return NULL for a mismatched user.

So here is an alternative:

SELECT vraag.id_categorie, vraag.id, vraag.niveau, vraag.omschrijving, 
       vraag.volgorde, vraag.code, users.Name, userantwoord.id_antwoord
FROM   dbo.categorie 
       INNER JOIN dbo.vraag 
         ON categorie.id = vraag.id_categorie
        CROSS JOIN dbo.Users 
        LEFT OUTER JOIN dbo.userantwoord 
        ON Users.UserId = userantwoord.Id_user AND vraag.id = userantwoord.id_vraag
WHERE categorie.link = @Onderwerp AND vraag.niveau = @Niveau 
      AND ([Users].UserId = @UserID OR @UserID is null) -- **
ORDER BY Users.Name, vraag.volgorde;

If you will only be using the query for a single user, then you will not need the @UserID is null predicate.

SqlFiddle example here

Different outcomes between Azure SQL and SQL Server Standard

1 Answers