SELECT t1.[Товар], t1.[Спецификация], t2.[Составные]
FROM [Таблица 1] AS t1
JOIN [Таблица 2] AS t2 ON t2.[Спецификация] = t1.[Спецификация]

DROP TABLE IF EXISTS [#Товар 1];
CREATE TABLE [#Товар 1](
	[Товар] VARCHAR(100) NOT NULL,
	[Спецификация] INT NULL
);
INSERT INTO [#Товар 1]([Товар], [Спецификация])
SELECT [Товар], [Спецификация]
FROM (VALUES ('Машина', 1)
			,('Холодильник', 2)
			,('Автомотор', 3)
			,('Холодильный мотор', 4)
			,('Цилиндры', NULL)
			,('Коленвал', NULL)
			,('Ротор', NULL)
			,('Фрион', NULL)) AS t([Товар], [Спецификация]);

DROP TABLE IF EXISTS [#Товар 2];
CREATE TABLE [#Товар 2](
	[Спецификация] INT NOT NULL,
	[Составные] VARCHAR(100) NOT NULL
);

INSERT INTO [#Товар 2]([Спецификация], [Составные])
SELECT [Спецификация], [Составные]
FROM (VALUES (1, 'Автомотор')
            ,(1, 'Корпус')
            ,(2, 'Корпус')
            ,(2, 'Холодильный мотор')
            ,(3, 'Цилиндры')
            ,(3, 'Коленвал')
            ,(4, 'Ротор')
            ,(4, 'Фрион')
) AS t([Спецификация], [Составные]);

WITH CTE AS (SELECT t1.[Товар], t1.[Спецификация], t2.[Составные]
             FROM [#Товар 1] AS t1
             JOIN [#Товар 2] AS t2 ON t2.[Спецификация] = t1.[Спецификация]
             WHERE t1.[Товар] NOT IN (SELECT [Составные] FROM [#Товар 2])
             UNION ALL
             SELECT t1.[Товар], t1.[Спецификация], t2.[Составные]
             FROM [#Товар 1] AS t1
             JOIN [#Товар 2] AS t2 ON t2.[Спецификация] = t1.[Спецификация]
             JOIN CTE AS c ON c.Составные = t1.Товар)

SELECT [Товар], [Спецификация], [Составные]
FROM CTE;