Will a join still work if the column being joined is null?

Question

I've got this stored procedure, and when I add the join I get no results. Is this because the column used for the join condition, ForClientID, is null? Would that affect the results of the table? If i select the table with Appointment.ForClientID instead of Client.Name, the rest of the table loads with the ForClientID column being null. Thanks in advance, this is an amazingly helpful community.

Create procedure GetAppointmentsByProfessionalName(@ProfessionalName varchar(256))
as
    declare @ProfessionalID uniqueidentifier
    set @ProfessionalID = (select UserId from aspnet_Users where UserName = @ProfessionalName)

    select  Appointment.AppointmentID as 'Appointment ID', 
            Client.Name as 'Client Name',
            CONVERT(VARCHAR(10),Appointment.ProposedDate,111) as 'Date', 
            CONVERT(CHAR(8), Appointment.ProposedTime, 114)as  'Time', 
            Appointment.ClientDescription as 'Client Notes', 
            Appointment.Confirmed as 'Confirmed Appointment'
    from Appointment join Client on Client.ClientID = Appointment.ForClientID
    where Appointment.ForProfessionalID = @ProfessionalID
go

Answer 1

The behavior surrounding NULLs is the primary difference between the different types of JOIN. Read up on http://en.wikipedia.org/wiki/Join_%28SQL%29 , which while being Wikipedia is a surprisingly good article on the topic.

Answer 2

Here's a re-write using an OUTER JOIN to take account of the Appointment.ForClientID being NULL-able and replacing the local variable @ProfessionalID with an INNER JOIN:

CREATE PROCEDURE GetAppointmentsByProfessionalName
@ProfessionalName VARCHAR(256)
AS
BEGIN

   SELECT Appointment.AppointmentID AS 'Appointment ID', 
          COALESCE(Client.Name, '{{NO CLIENT}}') AS 'Client NAME',
          CONVERT(VARCHAR(10),Appointment.ProposedDate,111) AS 'Date', 
          CONVERT(CHAR(8), Appointment.ProposedTime, 114)AS  'Time', 
          Appointment.ClientDescription AS 'Client Notes', 
          Appointment.Confirmed AS 'Confirmed Appointment'
     FROM Appointment 
          LEFT OUTER JOIN Client 
             ON Client.ClientID = Appointment.ForClientID
          INNER JOIN aspnet_Users
             ON aspnet_Users.UserId = Appointment.ForProfessionalID
    WHERE aspnet_UsersUserName = @ProfessionalName;

END;