--suppose i need to show all data from Employee table (NationalityName,BranchName,JobName) where

--NationalityID =NULL OR BranchID=NULL OR JobID=NULL

--WHAT I DO

-- Use outer joins on those columns, like this:

SELECT

n.NationalityName, e.DriverName, e.DriverID, e.NationalityID, e.ResidentNo, c.CountryName,

e.CountryID, b.BranchName, e.BranchID, e.JoinDate, e.ResignDate, e.HealthCarNo, e.JobID,

j.JobName, d.DepartmentName, j.DepartmentID, e.PlaceIssue, e.Deduction, e.ExpireDateMedical,

e.PolicyNumber, e.[Owner], e.[Version], e.ExpireDateResident, e.Salary, e.SexID, dbo.Sex.SexType,

dbo.Sex.FlagSex, e.MiritialID, m.MiritualStatus, s.StatusType, e.StatusID, e.UnactiveReason,

e.BirthDate, e.DateToday, e.UserID, e.PassportNo, e.Period, e.AccountNo, e.Bonus, e.AccountType,

e.PlaceOfBirth, e.EmplyeeName, e.ExpireDateresidentHijri, e.PassportDateStart, e.PassportDateExpire,

r.ReligonName, e.ReligonID

FROM dbo.Employee e

left JOIN dbo.Nationality n

ON n.NationalityID = e.NationalityID

INNER JOIN dbo.Country c

ON e.CountryID = c.CountryID

left JOIN dbo.Branch b

ON e.BranchID = b.BranchID

left JOIN dbo.Jobs j

INNER JOIN dbo.Department d

ON j.DepartmentID = d.DepartmentID -- Note

ON e.JobID = j.JobID -- Note

INNER JOIN dbo.Sex

ON e.SexID = dbo.Sex.SexID

INNER JOIN dbo.Miritial m

ON e.MiritialID = m.MiritialID

INNER JOIN dbo.[Status]

ON e.StatusID = s.StatusID

INNER JOIN dbo.Religon r

ON e.ReligonID = r.ReligonID

-- Note: You can join these two tables as shown, or alternatively inner join them together in

-- a subquery which is then outer-joined to the main query.