CREATE TABLE TableA
(
	ID INT IDENTITY PRIMARY KEY,
	Val VARCHAR(10)
)

CREATE TABLE LinkTable
(
	ID INT IDENTITY PRIMARY KEY,
	TableA_ID INT,
	TableB_ID INT
)

CREATE TABLE TableB
(
	ID INT IDENTITY PRIMARY KEY,
	Val VARCHAR(10)
)

SELECT
	A.ID
FROM TableA A
LEFT JOIN LinkTable ON	TableA_ID = A.ID
					AND TableB_ID = ISNULL(@TableB_ID, TableB_ID)

SELECT
	A.ID
FROM TableA A
LEFT JOIN LinkTable ON	TableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE TableB_ID END) = TableB_ID

DECLARE @TableB_ID INT

IF @TableB_ID IS NULL
	SELECT
		ID
	FROM TableA
ELSE
	SELECT
		A.ID
	FROM TableA A
	JOIN LinkTable ON	TableA_ID = A.ID
					AND TableB_ID = @TableB_ID

SELECT
	A.ID
FROM TableA A
WHERE 
(
	CASE
		WHEN @TableB_ID IS NOT NULL THEN
			CASE
				WHEN EXISTS (SELECT 1 FROM LinkTable WHERE TableA_ID = A.ID AND TableB_ID = @TableB_ID) THEN 1
				ELSE 0
			END
		ELSE 1
	END
) = 1

SELECT
	A.ID
FROM TableA A
LEFT JOIN LinkTable ON	TableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE ISNULL(TableB_ID, -1) END) = ISNULL(TableB_ID, -1)

        DECLARE @TableB_ID INT

	SELECT DISTINCT 
		A.ID
	FROM TableA A
	LEFT JOIN LinkTable LT ON LT.TableA_ID = A.ID 
	WHERE @TableB_ID IS NULL
	     OR LT.TableB_ID = @TableB_ID	 

          select *
	  from (values (1),(2),(3)) c(c)
	  where not (c = null)

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA

IF OBJECT_ID('tempdb..#LinkTable') IS NOT NULL
DROP TABLE #LinkTable

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
DROP TABLE #TableB

CREATE TABLE #TableA (
    [ID] INT NOT NULL,
    [Val] VARCHAR(10) NULL,
    PRIMARY KEY (ID),
    UNIQUE (ID))

CREATE TABLE #LinkTable(
    [ID] INT IDENTITY(1,1) NOT NULL,
    [TableA_ID] INT NULL,
    [TableB_ID] INT NULL,
    PRIMARY KEY (ID),
    UNIQUE (ID))

CREATE TABLE #TableB (
    [ID] INT NOT NULL,
    [Val] VARCHAR(10) NULL,
    PRIMARY KEY (ID),
    UNIQUE (ID))
        
INSERT INTO #TableA
SELECT 1,'Apples'
UNION
SELECT 2,'Pears'
UNION
SELECT 3,'Oranges'
UNION
SELECT 4,'Grapes'

INSERT INTO #TableB
SELECT 1,'Oranges'
UNION
SELECT 2,'Onions'
UNION
SELECT 3,'Apples'
UNION
SELECT 4,'Squash'

INSERT INTO #LinkTable
SELECT 1,3
UNION
SELECT 3,1


DECLARE @strSearch VARCHAR(50)
SET @strSearch = 'Apples'
--SET @strSearch = 'Pears'
--SET @strSearch = 'Oranges'
--SET @strSearch = 'Onions'
--SET @strSearch = ''

SELECT 
    a.ID
   ,a.Val 
FROM 
    #TableA AS a
LEFT OUTER JOIN
    #LinkTable AS lt
    ON a.ID = lt.TableA_ID
LEFT OUTER JOIN  
    #TableB AS b
    ON lt.TableB_ID = b.ID
WHERE
    
    --if @strSearch in A and in B then matching row from A
    ((a.Val = ISNULL(NULLIF(@strSearch,''),0)
     AND (b.Val = ISNULL(NULLIF(@strSearch,''),0)))
     
    --if @strSearch not A or not B then ALL rows from A
    OR
    ((NOT EXISTS (SELECT ID FROM #TableA WHERE val = @strSearch)
        OR NOT EXISTS (SELECT ID FROM #TableB WHERE val = @strSearch))
        AND EXISTS (SELECT ID FROM #TableA)))