How to get all records from one table and see if they are used in another without a sub query
You can use a LEFT JOIN to get the same results. Note how the WHERE condition of the subquery was moved to the ON clause of the join:

SELECT 
    uc.UserContractId, 
    uc.UserContractName,
    oc.OrderID
FROM 
    tblUserContracts AS uc
    LEFT JOIN tblOrderContracts AS oc 
    ON  uc.UserContractID = oc.UserContractID 
        AND oc.OrderID = 466 
        AND oc.OrderContractStatus = 'Active'
WHERE 
    uc.UserName = 'vandel212' 
    AND uc.UserContractStatus = 'Active' ;