правильный синтаксис для запроса sql и альтернатива WHERE EXISTS

Это должно сработать для вас:

SELECT  *
FROM    Jobs
WHERE   EXISTS
        (   SELECT  1
            FROM    Users
            WHERE   Users.UserID = @UserID
            AND (   (Users.Profession1 = Jobs.Profession AND Users.SubProfession1 IN (Jobs.SubProfession, 0))
                OR  (Users.Profession2 = Jobs.Profession AND Users.SubProfession2 IN (Jobs.SubProfession, 0))
                OR  (Users.Profession3 = Jobs.Profession AND Users.SubProfession3 IN (Jobs.SubProfession, 0))
                OR  (Users.Profession4 = Jobs.Profession AND Users.SubProfession4 IN (Jobs.SubProfession, 0))
                )
        );

Пример скрипта SQL

ИЗМЕНИТЬ

Поскольку это SQL-сервер, вы можете использовать CROSS APPLY..VALUES для разворота данных, таким образом вы можете выполнить внутреннее соединение и установить, соответствует ли задание, потому что подпрофессия равна 0, или есть ли точное совпадение подпрофессии:

WITH UserProf AS
(   SELECT  DISTINCT
            Users.UserID,
            p.Profession,
            p.SubProfession
    FROM    Users
            CROSS APPLY
            (VALUES
                (Profession1, SubProfession1),
                (Profession2, SubProfession2),
                (Profession3, SubProfession3),
                (Profession4, SubProfession4)
            ) p (Profession, SubProfession)
    WHERE   Users.UserID = @UserID
)
SELECT  Jobs.*, 
        MatchType = CASE WHEN MIN(UserProf.SubProfession) = 0 THEN 'All Subprofession' ELSE 'Exact subprofession' END
FROM    Jobs
        INNER JOIN UserProf
            ON UserProf.Profession = Jobs.Profession
            AND UserProf.SubProfession IN (0, Jobs.SubProfession)
GROUP BY Jobs.JobID, Jobs.Profession, Jobs.SubProfession;

Пример SQL-Fiddle

Я бы скорее посмотрел на самодокументируемый запрос, написанный два года назад, чем на запрос, в котором мне нужно понять, что он делает. Конечно, что-то, что является загадкой для меня, может быть простым делом для кого-то другого.

Учитывая мощность нуля в ячейке подпрофессии, что означает «соответствие профессии, не беспокойтесь о подпрофессии», я бы подошел к запросу как к объединению двух наборов.

Набор 1 — это набор всех должностей, соответствующих профессиям пользователя, где подпрофессия = 0.
Набор 2 — это набор всех должностей, соответствующих паре профессия/подпрофессия пользователя.

Таким образом, первым шагом будет получение для каждого пользователя списка различных профессий, где подпрофессия не имеет значения. Это привязывается к работе по профессии.

Второй шаг — получить для каждого пользователя список различных диад, т. е. где субпрофессия имеет значение. Это увязывается с рабочими местами по профессии и подпрофессии.

Если вы хотите, каждое соединение может добавить столбец, указывающий MatchLevel, т. е. ProfessionOnly в первом наборе и Both во втором наборе, но тогда вы можете увидеть одну и ту же работу дважды, один раз для общего соответствия и один раз для конкретного соответствия.