Stream Analytics - How to do a FULL OUTER JOIN with reference data?

Question

I'm using Stream Analytics' T-SQL to do a full outer join of a stream input and a reference data input.

I've tried the following query but I keep getting an error saying "When joining stream and reference data, reference data must be on the right side".

I want to do a full outer join of the two - how do I do this if the reference data could never be on the left side?

With leftJoin AS (
    SELECT SA.[InputID], RD.[InputID]
    FROM StreamData AS SA
    LEFT JOIN ReferenceData AS RD
    ON SA.[InputID] = RD.[InputID]
),

rightJoin AS (
    SELECT SA.[InputID], RD.[InputID]
    FROM ReferenceData AS RD -- Causes the error 'cause it should be on the right
    LEFT JOIN StreamData AS SA
    ON SA.[InputID] = RD.[InputID]
)

SELECT *
INTO [StorageTable]
FROM leftJoin 
UNION 
SELECT *
FROM rightJoin

My expected output is something like this:


SA.[InputID]   |  RD.[InputID]
------------------------------
1              |            1
2              |         null
null           |            3
4              |            4

Don't CTEs require the column names to be different? In this case both CTEs have the column name `InputID` twice. — The Impaler, Jan 24 '19 at 01:56
Maybe your query is trying to create a brand new table `StorageTable` with two columns with the same name. Give the second column a different name. — The Impaler, Jan 24 '19 at 01:59
@TheImpaler I could be wrong but I think it should be fine to have the same column name as long as the table name is in front? Also, I tried changing the second column's name and I got this error "Schemas of streams in union should match exactly. Please use 'select' with columns in the same order." — CompilationError, Jan 24 '19 at 20:03

score 0 · Answer 1 · answered Jan 24 '19 at 22:29

This is a comment that doesn't fit in the comments section.

The point is that all column names on any table must always have unique names. You can't have two columns with the same name on a table.

When you run a SELECT INTO you are actually creating a new table. The table being created should have different names for each one of its columns. Otherwise the database engine will reject the creation of this table.

I was thinking you should try giving the second column a different name. For example:

With leftJoin AS (
    SELECT
      SA.[InputID], 
      RD.[InputID] as rd_inputid -- added an alias to the column
    FROM StreamData AS SA
    LEFT JOIN ReferenceData AS RD
    ON SA.[InputID] = RD.[InputID]
),    
rightJoin AS (
    SELECT
      SA.[InputID], 
      RD.[InputID] as rd_inputid -- added an alias to the column
    FROM ReferenceData AS RD
    LEFT JOIN StreamData AS SA
    ON SA.[InputID] = RD.[InputID]
)    
SELECT *
INTO [StorageTable]
FROM leftJoin 
UNION 
SELECT *
FROM rightJoin

I hope it helps.

Thanks for that. I've added an alias to the second column but it's still giving the original error: "When joining stream and reference data, reference data must be on the right side" because in the rightJoin ReferenceData is still on the left and since ASA only accepts LEFT JOIN, ReferenceData will need to be on the left to create a RIGHT JOIN. Unless there's another way of doing it? — CompilationError, Jan 24 '19 at 22:51

score 0 · Answer 2 · answered Jan 25 '19 at 01:23

0

Probably sample data would have helped, but I think based on your output, full outer join is required and should give the output.

 SELECT distinct SA.[InputID] as InputIDfromSA, RD.[InputID] as InputIDfromRD
 FROM ReferenceData AS RD  
 Full outer JOIN StreamData AS SA
 ON SA.[InputID] = RD.[InputID]

answered Jan 25 '19 at 01:23

Avi

1,795
3
16
29

1

Hi, Stream Analytics doesn't allow a FULL OUTER JOIN that's why I'm trying to do a LEFT and RIGHT JOIN instead. The problem is that Strem Analytics doesn't allow reference data to be on the right for a RIGHT JOIN and left for a LEFT JOIN. – CompilationError Jan 28 '19 at 19:46

Stream Analytics - How to do a FULL OUTER JOIN with reference data?

2 Answers2