Select all data include another table even if null

Question

Setup: (1) Employee table (employeeID, firstName, lastName)

(1) Shoe table (shoeID, Employee_employeeID, shoeName, shoeColor, shoeBrand)

I want to select all rows in Employee table and even if there is no matching EmployeeID (Employee_EmployeeID) in the shoe table display that entire row anyway. Example desired output:

 EmployeeID  | firstName    | lastName    | shoeName     |   shoeColor  | shoeBrand

 1            John           Smith         AirMax2          Red          Nike
 2            Ronald         Mcdonald      null             null         null
 3            James          Knight        null             null         null
 4            Cindy          Smith         Pump             Brown        Cole Haan

I have tried a lot of different joins and I will get duplicate rows for each Employee.

Answer 1

try this

   SELECT e.employeeId,e.firstName,e.lastName,s.ShoeName,s.ShoeColor,s.ShoeBrand 
   FROM    Employee e
   LEFT JOIN Shoe s
   ON e.employeeID = s.Employee_employeeID

DEMO SQLFIDDLE HERE

Answer 2

You are going to want to use a LEFT JOIN:

select e.employeeId,
  e.firstName,
  e.lastName,
  s.ShoeName,
  s.ShoeColor,
  s.ShoeBrand
from Employee e
left join shoe s
  on e.employeeID = s.Employee_employeeID

See SQL Fiddle with Demo

A LEFT JOIN will return the matching rows from the employee table even if there is not a matching row in the shoe table.

If you need help learning JOIN syntax, here is a great visual explanation of joins.

Now, if you wanted all rows that match both tables, then you would use an INNER JOIN.