CODEWITHSUNDEEP
sqlpostgresqljoin
FireHawk2300
FireHawk2300

Reputation: 97

Display all results queried on PostgreSQL where the JOINING value is missing

I have two tables on PostgreSQL, namely: employees and employers. There is data in each of these tables.

Columns in employees: employee_id, employee_name, employer_id.

Columns in employers: employer_id, employer_name.

I want to display all employee_name's that don't have an associating employer_name.

I used the below query:

    SELECT DISTINCT a.employee_name, b.employer_name
    FROM employees a
    NATURAL JOIN employers b
    WHERE a.employee_name LIKE 'Jack';

NB! I have also tried adding in the below to my query: COALESCE(b.employer_name, '') = ''

Problem: If there is no record in the employer table containing the associating employee_id value, the query returns nothing all. I am assuming this is because there is nothing for the two tables to join on?... But I would like to at least find all employees that don't have an employer. I would ideally like the employer_name value in my result to either return: blank/''/NULL.

Your assistance is greatly appreciated.

Upvotes: 0

Views: 859

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Your table name employees table is a little odd, IMO. There is a design 'rule of thumb' that says a table models either an entity or the relationship between entities but not both. To demonstrate this oddness, consider the relational operation is

employees MINUS employers

...which suggests something is off.

Another symptom of this design problem is that the employer_id in the employees table must have some kind of placeholder to represent the predicate employee has no employer, possibly a null (and nulls are to be avoided, IMO).

I suggest you fix this design by introducing a third table to model the relationship between an employee and their employer. Herein lies another design problem: shouldn't this new table be named employees? In other words, isn't the very definition of an employee a person who has an employer?

Consider this design instead:

People: person_id, person_name
Employers: employer_id, employer_name
Employees: employer_id, person_id

To find the names of people who are not employees in pseudo-relational notation:

People MINUS Employees { person_name }

SQL is quite a bit more verbose:

SELECT DISTINCT person_name
  FROM People
       NATURAL JOIN
       ( SELECT person_id FROM People
         EXCEPT
         SELECT person_id FROM Employees ) t;

Note your original query needlessly uses range variables a and b. One of the benefits of NATURAL JOIN is the elminiation of duplicate column names. Range variables with NATURAL JOIN always looks odd to me!

Upvotes: 0

鄭有維
鄭有維

Reputation: 265

select employees.employee_name ,employers.employer_name
from employees
left join employers 
on employees.employee_id = employees.employee_id
where employers.employer_name is NULL

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Use a LEFT JOIN:

SELECT a.employee_name,
       COALESCE(b.employer_name, 'NA') AS employer_name
FROM employees a
LEFT JOIN employers b
    ON a.employer_id = b.employer_id
WHERE b.employer_id IS NULL

Your current query has several problems, first of which is that you are using a NATURAL JOIN, which should behave link an INNER JOIN, discarding employee records which do not match to any employer. Instead, by using LEFT JOIN, we can retain all employee records, regardless of whether or not they have an employer. The WHERE clause checks for NULL in the joined table, with NULL indicating that the employee did not match to any employer.

Upvotes: 0

Related Questions