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

Question

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.

Answer 1

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