Azure SQL DB not returning correct results

Question

Azure SQL DB not returning correct results

Woebbeking, William K 0

Hello All,

I have a stream analytics job that is left outer joining an event hub dataset and a sql db reference table. When I left outer join the datasets, the result returns null:

JoinTable

You can see the result in the picture. The column called LEDT should return the datetime of the data from the left outer join. It does not consistently. If you see the row below, it works as intended. What I am noticing is the dataset is only returning a small amount of the data. The row count should be something like 1200 rows in the reference data. But the result set is only returning 50 rows. I have messed with the streaming units, to no avail. I also thought maybe it was just a result of a small sample dataset being returned for rendering issues. Even though I know 1200 rows is not a lot of data. I have also tried to add the dataset with a csv to a storage account, but that also does not seem to work.

Here's a snapshot of the left outer join:

LeftOuterJoin Again, I don't think it's an issue with the left outer join or else the second row wouldn't return correctly.

Any thoughts on the issue and a fix?

Thanks in advance!

Smaran Thoomu 21,320 Reputation points Microsoft External Staff

2025-03-03T15:51:53.44+00:00
Hi @Woebbeking, William K
Thanks for reaching out! I understand how frustrating it can be when your data isn’t showing up as expected. Based on what you described, here are a few things you might want to check:

Reference Data Limitations – Stream Analytics doesn’t always load the full reference dataset if it's too large. Try restarting the job to refresh the data.

Data Type Mismatch – Make sure the columns you're joining on have the same data type in both datasets. Even small differences can cause missing results.

Timing Issues – If your event data and reference data don’t match up in terms of time, some rows might not join properly. Adding a time windowing function might help.

NULL Values – If there are empty or NULL values in your reference table, they might be preventing the join from working as expected.

Since one row seems to be working while others don’t, it suggests the join itself is working, but something is preventing all matches. Try restarting the job first and checking the data types.

For more information you can refer this article: https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-troubleshoot-output

I hope this helps! Let me know if you need further assistance.
Rakesh Govindula 95 Reputation points Microsoft External Staff

2025-03-04T12:47:23.21+00:00
Hi @Woebbeking, William K ,

You have mentioned that the reference dataset should have 1200 rows, but only 50 appearing, can you please verify the SQL reference dataset contains?

Also, try to increase the Stream Analytics streaming units and monitor the behavior.

Try to cross verify the same data from any CSV or JSON dataset instead of SQL dataset.

If you still facing the issue, please provide the sample input data and expected output data for the clarification of the issue.
AnnuKumari-MSFT 34,271 Reputation points Microsoft Employee

2025-03-10T10:09:00.39+00:00

Woebbeking, William K ,

Just following up to see if the above suggestion was helpful. In case the answer helped, kindly take out a moment to accept the answer by clicking on Accept answer button. Thankyou

1 answer

Your answer

Smaran Thoomu 21,320 Reputation points Microsoft External Staff

2025-03-03T15:51:53.44+00:00

Hi @Woebbeking, William K
Thanks for reaching out! I understand how frustrating it can be when your data isn’t showing up as expected. Based on what you described, here are a few things you might want to check:

Reference Data Limitations – Stream Analytics doesn’t always load the full reference dataset if it's too large. Try restarting the job to refresh the data.

Data Type Mismatch – Make sure the columns you're joining on have the same data type in both datasets. Even small differences can cause missing results.

Timing Issues – If your event data and reference data don’t match up in terms of time, some rows might not join properly. Adding a time windowing function might help.

NULL Values – If there are empty or NULL values in your reference table, they might be preventing the join from working as expected.

Since one row seems to be working while others don’t, it suggests the join itself is working, but something is preventing all matches. Try restarting the job first and checking the data types.

For more information you can refer this article: https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-troubleshoot-output

I hope this helps! Let me know if you need further assistance.
Rakesh Govindula 95 Reputation points Microsoft External Staff

2025-03-04T12:47:23.21+00:00

Hi @Woebbeking, William K ,

You have mentioned that the reference dataset should have 1200 rows, but only 50 appearing, can you please verify the SQL reference dataset contains?

Also, try to increase the Stream Analytics streaming units and monitor the behavior.

Try to cross verify the same data from any CSV or JSON dataset instead of SQL dataset.

If you still facing the issue, please provide the sample input data and expected output data for the clarification of the issue.
AnnuKumari-MSFT 34,271 Reputation points Microsoft Employee

2025-03-10T10:09:00.39+00:00

Woebbeking, William K ,

Just following up to see if the above suggestion was helpful. In case the answer helped, kindly take out a moment to accept the answer by clicking on Accept answer button. Thankyou

Answer 1

Woebbeking, William K ,

Left outer join specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

Azure Stream Analytics supports both inner join (the default) and LEFT outer join. For an inner join, a result is only returned when a match is found. But for a LEFT OUTER join, if an event from the left side of the join is unmatched, a row with NULL for all the columns of the right row is returned. For instance, here is an example to find the absence of events.

Reference: https://learn.microsoft.com/en-us/stream-analytics-query/join-azure-stream-analytics

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Share via

Azure SQL DB not returning correct results

1 answer

Your answer