RIGHT\LEFT Join does not provide null values without condition

Question

I have two tables one is the lookup table and the other is the data table. The lookup table has columns named cycleid, cycle. The data table has SID, cycleid, cycle. Below is the structure of the tables.

If you check the data table, the SID may have all the cycles and may not have all the cycles. I want to output the SID completed as well as missed cycles.

I right joined the lookup table and retrieved the missing as well as completed cycles. Below is the query I used. 

 SELECT TOP 1000 [SID]

          ,s4.[CYCLE]

          ,s4.[CYCLEID]

      FROM [dbo].[data] s3 RIGHT JOIN

[dbo].[lookup_data] s4 ON s3.CYCLEID = s4.CYCLEID 

The query is not displaying me the missed values when I query for all the SID's. When I specifically query for a SID with the below query I am getting the correct result including the missed ones.

SELECT TOP 1000 [SID]

      ,s4.[CYCLE]

      ,s4.[CYCLEID]

  FROM [dbo].[data] s3 RIGHT JOIN [dbo].[lookup_data] s4 

  ON s3.CYCLEID = s4.CYCLEID 

  AND s3.SID = 101002

  ORDER BY [SID], s4.[CYCLEID]

As I am supplying this query into tableau I cannot provide the sid value in the query. I want to return all the sid's and from tableau, I will be doing the rest of the things.

The expected output that I need is as shown below. 

I wrote a cross join query like below to achieve my expected output

SELECT DISTINCT

            tab.CYCLEID

           ,tab.SID

           ,d.CYCLE

  FROM      ( SELECT    d.SID

                       ,d.[CYCLE]

                       ,e.CYCLEID

              FROM      ( SELECT    e.sid

                                   ,e.CYCLE

                          FROM      [db_temp].[dbo].[Sheet3$] e

                        ) d

              CROSS JOIN [db_temp].[dbo].[Sheet4$] e

            ) tab

  LEFT OUTER JOIN [db_temp].[dbo].[Sheet3$] d

  ON        d.CYCLEID = tab.CYCLEID

            AND d.SID = tab.SID

  ORDER BY  tab.SID

           ,tab.CYCLEID;

However, I am not able to use this query for more scenarios as my data set to have nearly 20 to 40 columns and I am having issues when I use the above one.

Is there any way to do this in a simpler manner with only left or right join itself? I want the query to return all the missing values and the completed values for all the SID's instead of supplying a single sid in the query. 

Answer 1

• You can create a master table first (combine all SID and CYCLE ID), then right join with the data table

;with ctxMaster as (

    select distinct d.SID, l.CYCLE, l.CYCLEID

    from lookup_data l

    cross join data d

)

select d.SID, m.CYCLE, m.CYCLEID

from ctxMaster m

left join data d on m.SID = d.SID and m.CYCLEID = d.CYCLEID

order by m.SID, m.CYCLEID

• Click on this link to see the execution: Fiddle

• In case, if you don't want to use common table expression, subquery version:

select d.SID, m.CYCLE, m.CYCLEID

from (select distinct d.SID, l.CYCLE, l.CYCLEID

      from lookup_data l

      cross join data d) m

left join data d on m.SID = d.SID and m.CYCLEID = d.CYCLEID

order by m.SID, m.CYCLEID