Pergunta sobre junções com múltiplas tabelas

CREATE TABLE #main
(
    ID INT
  , firstName VARCHAR(12)
);

CREATE TABLE #worker
(
    ID INT
  , WorkerID INT
);

CREATE TABLE #location
(
    ID INT
  , Location VARCHAR(12)
);

CREATE TABLE #middlename
(
    ID INT
  , MiddleName VARCHAR(12)
);

INSERT INTO #main
VALUES
(1, 'Jim')
, (2, 'John');

INSERT INTO #worker
VALUES
(1, 123)
, (1, 2345)
, (2, 4534);

INSERT INTO #location
VALUES
(1, 'Asia')
, (1, 'America')
, (1, 'Africa');

INSERT INTO #middlename
VALUES
(2, 'Jack');

Se você está apenas tentando entrar nas mesas com base no que disse nos comentários, faça o seguinte -

SELECT m.ID
     , m.firstName
     , w.WorkerID
     , l.Location
     , mn.MiddleName
FROM #main AS m
    LEFT JOIN #worker AS w
        ON w.ID = m.ID
    LEFT JOIN #location AS l
        ON l.ID = m.ID
    LEFT JOIN #middlename AS mn
        ON mn.ID = m.ID
WHERE m.ID = 1;

Mas se você está tentando obter aquele resultado estranho, é lixo, mas faça isso -

SELECT base.ID
     , base.firstName
     , IIF(base.ID = w.ID, w.WorkerID, NULL) AS WorkerID
     , base.Location
     , base.MiddleName
FROM
(
    SELECT m.ID
         , m.firstName
         , l.Location
         , mn.MiddleName
         , ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY (SELECT NULL)) AS rn
    FROM #main AS m
        LEFT JOIN #location AS l
            ON l.ID = m.ID
        LEFT JOIN #middlename AS mn
            ON mn.ID = m.ID
    WHERE m.ID = 1
) AS base
    INNER JOIN
    (
        SELECT ID
             , WorkerID
             , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        FROM #worker
    ) AS w
        ON base.rn = w.rn;

demonstração DB Fiddle