sql Cross join UNION ALL

select * from (select rc.RightCode,rc.RightName,rt.RightType,rt.TypeName,rt.TableName from dbo.RightCode as rc cross join dbo.RightType as rt)aa 
cross join dbo.Devices where aa.RightType=1
多表连接查询
select ViewRoleRightsDetail.*,devices.Name from
(SELECT     dbo.RightCode.RightName, dbo.RightType.TypeName, dbo.Roles.RoleID, dbo.RightsNew.RightType, dbo.RightsNew.RightItemId,
                      dbo.RightType.TableName, dbo.RightCode.RightCode, dbo.RoleRightsNew.RightId
FROM         dbo.Roles INNER JOIN
                      dbo.RoleRightsNew ON dbo.Roles.RoleID = dbo.RoleRightsNew.RoleId INNER JOIN
                      dbo.RightsNew ON dbo.RoleRightsNew.RightId = dbo.RightsNew.RightId INNER JOIN
                      dbo.RightType ON dbo.RightsNew.RightType = dbo.RightType.RightType INNER JOIN
                      dbo.RightCode ON dbo.RightsNew.RightCode = dbo.RightCode.RightCode) ViewRoleRightsDetail
left join devices on  devices.DeviceID = ViewRoleRightsDetail.rightItemId
where tablename='devices'
UNION ALL
select ViewRoleRightsDetail.*,sensors.Name
from (SELECT     dbo.RightCode.RightName, dbo.RightType.TypeName, dbo.Roles.RoleID, dbo.RightsNew.RightType, dbo.RightsNew.RightItemId,
                      dbo.RightType.TableName, dbo.RightCode.RightCode, dbo.RoleRightsNew.RightId
FROM         dbo.Roles INNER JOIN
                      dbo.RoleRightsNew ON dbo.Roles.RoleID = dbo.RoleRightsNew.RoleId INNER JOIN
                      dbo.RightsNew ON dbo.RoleRightsNew.RightId = dbo.RightsNew.RightId INNER JOIN
                      dbo.RightType ON dbo.RightsNew.RightType = dbo.RightType.RightType INNER JOIN
                      dbo.RightCode ON dbo.RightsNew.RightCode = dbo.RightCode.RightCode) ViewRoleRightsDetail
left join sensors on  sensors.SensorID = ViewRoleRightsDetail.rightItemId
where tablename='sensors'