Type of Queries for Denormalized Dataset

SSAS and SSRS
1

In SSRS, unlike Crystal Report, USUALLY, it works with one denomalize dataset throughout a report, especially with nested data. This dataset can fullfill different sections of the report as I break them up by combination of fields.

I understand the following three queries do not produce the same data. However, what I am trying to understand is which one is recommended for performance and structure? I usually tend to use the INNER and LEFT joins but as I have analyzed through the months, the FULL OUTER JOIN query may also work better. The CROSS join query is also a good way to connect several temp tables or sub correlated queries together.

Thoughts please???

---------------------------------------------------------------------------------------
SELECT * FROM
(
	SELECT
		  '1'		AS Section01_F1
		, '2'		AS Section01_F2
) T1

CROSS JOIN

(
	SELECT
		  '3a'		AS Section02_F3
		, '4a'		AS Section02_F4
	UNION ALL
	SELECT
		  '3b'		AS Section02_F3
		, '4b'		AS Section02_F4
) T2

CROSS JOIN

(
	SELECT
		  '5a'		AS Section03_F5
		, '6a'		AS Section03_F6
	UNION ALL
	SELECT
		  '5b'		AS Section03_F5
		, '6b'		AS Section03_F6
	UNION ALL
	SELECT
		  '5c'		AS Section03_F5
		, '6c'		AS Section03_F6
) T3
---------------------------------------------------------------------------------------
SELECT * FROM
(
	SELECT
		  'T1'	AS My_ID
		, '1'		AS Section01_F1
		, '2'		AS Section01_F2
) T1

FULL OUTER JOIN
(
	SELECT
		  'T2'	AS My_ID
		, '3a'		AS Section02_F3
		, '4a'		AS Section02_F4
	UNION ALL
	SELECT
		  'T2'	AS My_ID
		, '3b'		AS Section02_F3
		, '4b'		AS Section02_F4
) T2
ON T1.My_ID = T2.My_ID 

FULL OUTER JOIN
(
	SELECT
		  'T3'	AS My_ID
		, '5a'		AS Section03_F5
		, '6a'		AS Section03_F6
	UNION ALL
	SELECT
		  'T3'	AS My_ID
		, '5b'		AS Section03_F5
		, '6b'		AS Section03_F6
	UNION ALL
	SELECT
		  'T3'	AS My_ID
		, '5c'		AS Section03_F5
		, '6c'		AS Section03_F6
) T3
ON T1.My_ID = T3.My_ID 
---------------------------------------------------------------------------------------
SELECT * FROM
(
	SELECT
		  '123456789'	AS My_ID
		, '1'			AS Section01_F1
		, '2'			AS Section01_F2
) T1

INNER JOIN
(
	SELECT
		  '123456789'	AS My_ID
		, '3a'			AS Section02_F3
		, '4a'			AS Section02_F4
	UNION ALL
	SELECT
		  '123456789'	AS My_ID
		, '3b'			AS Section02_F3
		, '4b'			AS Section02_F4
) T2
ON T1.My_ID = T2.My_ID 

INNER JOIN
(
	SELECT
		  '123456789'	AS My_ID
		, '5a'			AS Section03_F5
		, '6a'			AS Section03_F6
	UNION ALL
	SELECT
		  '123456789'	AS My_ID
		, '5b'			AS Section03_F5
		, '6b'			AS Section03_F6
	UNION ALL
	SELECT
		  '123456789'	AS My_ID
		, '5c'			AS Section03_F5
		, '6c'			AS Section03_F6
) T3
ON T1.My_ID = T3.My_ID
2

Left outer join and cross join return more rows compare to inner join. So based on scenario you would decide which join to be use in the query. Coming to performance, inner join performs faster than other joins