Your contacts previously injected into the Journey would continue in the Journey, even though they no longer qualify for entry criteria, unless you've defined Exit Criteria for the Journey.
Decision Splits based on Contact Data use data as it is at the time of evaluation, not as it was at the time of entry. Decision Splits based on Journey Data evaluate data ...
This isn't really the forum for discussing SQL, unless there's some specific application of SQL relating to Marketing Cloud that only we can help with.
NULL doesn't equal NULL or anything else, "LIKE" or "NOT LIKE" included. NULL's not possible to evaluate. "LIKE '%Do not Mass Email%'" matches some stuff - the stuff you're ...
Queries created in Query Studio are wrapped inside other queries behind the scenes. I've seen database issues in the platform cause PK violations -- especially if the data extension has had any field revisions after it was first created. I'd open a support case.
My go-to for ensuring that PK violations do not occur is to partition by the target DE's ...
The Record_Type_ID alias returned by your query is not the same as Record Type ID in the error message.
Should probably be this:
c.AccountId as SubscriberID
, c.FirstName as First_Name
, c.LastName as Last_Name
, c.HasOptedOutOfEmail as Email_Opt_Out
, a.RecordTypeId as [Record Type ID]
FROM Contact_Salesforce ...