CODEWITHSUNDEEP
sqlsql-servert-sqloptimizationsql-server-2016
007
007

Reputation: 2186

TSQL - How to avoid UNION ALL

Sample Data:

DECLARE @Parent TABLE
    (
        [Id] INT
      , [Misc_Val] VARCHAR(5)
    ) ;

DECLARE @Children TABLE
    (
        [Id]   INT
      , [P_ID] INT
    ) ;

INSERT INTO @Parent
VALUES
    ( 1, 'One' )
  , ( 2, 'Two' )
  , ( 3, 'Three' )
  , ( 5, 'Four' ) ;

INSERT INTO @Children
VALUES
    ( 10, 1 )
  , ( 11, 1 )
  , ( 21, 2 )
  , ( 23, 2 )
  , ( 30, 3 )
  , ( 40, 4 ) ;

Goal: To efficiently output three fields ( [Id] and [IsChild], [Misc_Val] ). Output all records from @Parent table with [IsChild] = 0 and output all MATCHING records from @Child table (@Parent.Id = @Children.P_Id) with [IsChild] = 1.

Expected Output

Id  IsChild Misc_Val
1   0       One
2   0       Two
3   0       Three
5   0       Four
10  1       One
11  1       One
21  1       Two
23  1       Two
30  1       Three

My try:

SELECT  [P].[Id]
      , 0 AS [IsChild]
      , [P].[Misc_Val]
FROM    @Parent AS [P]
UNION ALL
SELECT  [C].[Id]
      , 1
      , [P].[Misc_Val]
FROM    @Parent AS [P]
JOIN    @Children AS [C]
ON      [C].[P_ID] = [P].[Id] ;

Is there a better way to do this than using UNION ALL? @Parent and @Children tables are quite big and so am trying to avoid querying the @Parent table twice.

UPDATE: The below answer made me realized something I missed out when creating the post with mocked data. We do need some additional data from @Parent table regardless in the final output.

Upvotes: 0

Views: 122

Answers (1)

Charlieface
Charlieface

Reputation: 71593

You can use CROSS APPLY to add the child table to the parent table.

This may or may not be faster, it can depend on indexing and so forth. You need to check the query plan.

SELECT  v.Id
      , v.IsChild
      , P.Misc_Val
FROM    @Parent AS P
CROSS APPLY (
    SELECT
        P.Id,
        0 AS IsChild
    UNION ALL
    SELECT
        C.Id,
        1
      FROM @Children AS C
      WHERE C.P_ID = P.Id
) v;

Note that the first SELECT in the apply has no FROM and therefore does not do any table access.

Upvotes: 3

Related Questions