SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.expiration (batch_number INT NOT NULL, fruit_number INT NOT NULL, store_number INT NOT NULL, expiration_date DATE NULL);
INSERT INTO dbo.expiration(batch_number, fruit_number, store_number, expiration_date)
VALUES
	(1, 3, 4, CAST(N'2021-11-25' AS DATE)),
	(1, 2, 2, CAST(N'2021-11-22' AS DATE)),
	(1, 5, 3, CAST(N'2021-11-30' AS DATE)),
	(2, 2, 7, NULL),
	(2, 3, 2, CAST(N'2021-12-12' AS DATE)),
	(1, 1, 5, NULL),
	(2, 1, 6, CAST(N'2021-11-28' AS DATE));
7 rows affected

CREATE TABLE dbo.fruits (fruit_number INT NOT NULL, fruit_name NVARCHAR(50) NOT NULL);
INSERT INTO dbo.fruits(fruit_number, fruit_name)
VALUES
	(1, N'banana'),
	(2, N'apple'),
	(3, N'pear'),
	(4, N'peach'),
	(5, N'strawberry');
5 rows affected

SELECT
  f.fruit_number, 
  f.fruit_name,
  expirationDate = CASE WHEN EXISTS (SELECT 1
                       FROM dbo.expiration AS e
                       WHERE e.fruit_number = f.fruit_number
                         AND e.expiration_date IS NULL
                     ) THEN 1 ELSE 0 END
FROM dbo.fruits AS f
ORDER BY f.fruit_number;


fruit_number
fruit_name
expirationDate


1
banana
1

2
apple
1

3
pear
0

4
peach
0

5
strawberry
0