sqlcaseapplyouter-apply

Using Outer Apply to Compare 2 columns of one table that match values with 1 column in a different table


I'm new to APPLY statements in SQL and I'm trying to make a solution using an APPLY statement.

I have two tables:

Original ID Target ID
SHAPE1 SHAPE2
SHAPE3 SHAPE4
Shape Color Num Sides Size
SHAPE1 Orange 3 Large
SHAPE2 Red 6 Small
SHAPE3 Orange 3 Large
SHAPE4 Green 6 Small

These tables describe Shape IDs and a 'target' Shape. The goal here is to determine if the characteristics of Original Shape are the same as Target Shape. The query would compare the two columns and return 'TRUE' if they shared the same characteristics.

So, in the second table-- Shape 1 is a twin of Shape 3 meaning a query should return 'true' for Shape 1 and Shape 3, but not for anything else as there isn't a perfect match. (note the color green/red are different for 2 and 4)

SELECT CASE WHEN
                 (SELECT COUNT(1) 
                 FROM table2 t2 (NOLOCK)
                 OUTER APPLY (SELECT t2.size, t2.color, t2.sides
                              WHERE t1.original_id = t2.shape
                              ) source
                 OUTER APPLY (SELECT t2.size, t2.color, t2.sides
                              WHERE t1.target_id = t2.shape
                              ) target
                WHERE (ISNULL(source.size, '') <> ISNULL(target.size, '')
                OR ISNULL(source.color, '') <> ISNULL(target.color, '')
                OR ISNULL(source.sides, '') <> ISNULL(target.sides, '')) > 0             
             THEN NULL ELSE 'TRUE'
END 

This was my attempt to do this as an outer apply. I've made the query work using both an 'EXISTS' and 'COUNT' statement, but I wanted to test my knowledge and see if it was ever possible using APPLYs. Unfortunately, this query doesn't yield correct results. It seems to think no matter what, there's always a difference between Shapes.


Solution

  • Well it is possible to use apply operators, but I sincerely don't believe it is worth the effort when simple joins will do the job. Note for the results below I added a row:

    INSERT INTO t1 (OriginalID, TargetID) VALUES
    ('SHAPE1', 'SHAPE2'),
    ('SHAPE3', 'SHAPE4'),
    ('SHAPE1', 'SHAPE3'); -- added
    
    SELECT
          t1.OriginalID AS OriginalShape
        , t1.TargetID AS TargetShape
        , CASE WHEN t2.Color = t3.Color
                AND t2.NumSides = t3.NumSides
                AND t2.Size = t3.Size THEN 'TRUE' ELSE 'FALSE' 
          END AS SameCharacteristics
        , t2.Color t2color
        , t3.Color t3color
        , t2.NumSides t2NumSides
        , t3.NumSides t3NumSides
        , t2.Size t2Size
        , t3.Size t3Size
    FROM t1
    CROSS APPLY 
        (SELECT * FROM t2 WHERE t1.OriginalID = t2.Shape) t2
    CROSS APPLY 
        (SELECT * FROM t2 WHERE t1.TargetID = t2.Shape) t3;
    
    OriginalShape TargetShape SameCharacteristics t2color t3color t2NumSides t3NumSides t2Size t3Size
    SHAPE1 SHAPE2 FALSE Orange Red 3 6 Large Small
    SHAPE1 SHAPE3 TRUE Orange Orange 3 3 Large Large
    SHAPE3 SHAPE4 FALSE Orange Green 3 6 Large Small
    SELECT
          t1.OriginalID AS OriginalShape
        , t1.TargetID AS TargetShape
        , CASE WHEN t2.Color = t3.Color
                AND t2.NumSides = t3.NumSides
                AND t2.Size = t3.Size THEN 'TRUE' ELSE 'FALSE' 
          END AS SameCharacteristics
        , t2.Color t2color
        , t3.Color t3color
        , t2.NumSides t2NumSides
        , t3.NumSides t3NumSides
        , t2.Size t2Size
        , t3.Size t3Size
    FROM t1
    JOIN t2 t2 ON t1.OriginalID = t2.Shape
    JOIN t2 t3 ON t1.TargetID = t3.Shape
    
    OriginalShape TargetShape SameCharacteristics t2color t3color t2NumSides t3NumSides t2Size t3Size
    SHAPE1 SHAPE2 FALSE Orange Red 3 6 Large Small
    SHAPE1 SHAPE3 TRUE Orange Orange 3 3 Large Large
    SHAPE3 SHAPE4 FALSE Orange Green 3 6 Large Small

    fiddle