How to get all records from one table and see if they are used in another without a sub query

Question

I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple query. Here are my two tables:

tblOrderContracts
--------------------------------------
OrderContractID           | bigint
OrderID                   | bigint
UserContractID            | bigint
UserName                  | varchar
OrderContractDateCreated  | datetime
OrderContractStatus       | varchar



tblUserContracts
-------------------------------------
UserContractId            | bigint
UserName                  | varchar
UserContractName          | varchar
UserContractFileName      | varchar
UserContractDateCreated   | datetime
UserContractStatus        | varchar

A user can apply any number of contracts to an order, so what I need to do is get a list of all the contracts, and see if they are used in a particular order. Right now, I use a sub query to get the job done, but I don't like using sub queries if I don't need to.

SELECT uc.UserContractId, uc.UserContractName,
(SELECT oc.OrderID FROM tblOrderContracts as oc WHERE uc.UserContractID = 
oc.UserContractID AND oc.OrderID = 466 AND OrderContractStatus = 
'Active')
FROM tblUserContracts as uc
WHERE uc.UserName = 'vandel212' AND UserContractStatus = 'Active'

Ideally I'd like to get a result set that looks like this where if value is NULL in the OrderID column, that means it's not used:

UserContractId  |  UserContractName  |  OrderID
-----------------------------------------------
36              |  test              |  466
37              |  test2             |  NULL
38              |  test3             |  NULL

Any help is greatly appreciated. Thanks!

Answer 1

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' ;