MySQL Statement Displaying Values Even If NULL

Question

I have two tables, EMPLOYEE & DEPENDENT. The employee PK is SSN and the foreign key for the dependent table is ESSN. I am trying to retrieve the name of all employees and the names of their dependent. If an employee does not have a dependent, I just need to display a blank value in the dependent column.

To retrieve the name of employees who have dependents, this is the statement I use:

SELECT 
      firstName AS "First Name", 
      lastName AS "Last Name",
      dependent_name AS "Dependent Name" 
FROM employee,dependent 
WHERE ssn=essn;

If somebody could guide me in the right direction, that would be greatly appreciated.

You need an outer join. See A Visual Explanation of SQL Joins for more information. — eggyal
– eggyal, Commented Mar 20, 2015 at 23:19

jpw · Accepted Answer · 2015-03-20 23:19:47Z

2

Use a left join to get all rows from the first table even if there are no matching in the second:

SELECT 
  firstName AS "First Name", 
  lastName AS "Last Name", 
  dependent_name AS "Dependent Name" 
FROM employee
LEFT JOIN dependent ON dependent.essn = employee.ssn;

I changed your implicit old-style join to an ANSI compliant explicit join too.

answered Mar 20, 2015 at 23:19

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dhint4 Over a year ago

Thanks a bunch. This worked like I wanted it to. I am going to dig into what a LEFT JOIN does to expand my knowledge. Will vote as best answer once I am able to.

jpw Over a year ago

@dhint4 The link that eggyal provided is a really good explanation.

Collectives™ on Stack Overflow

MySQL Statement Displaying Values Even If NULL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related