CODEWITHSUNDEEP
postgresqlsqlalchemy
limasxgoesto0
limasxgoesto0

Reputation: 4793

Querying a table for rows with no foreign keys pointing to them

Let's say we have two tables in our database.

Table 1:

Employer:
    id
    name

Table 2:

Employee:
    id
    name
    employer_id

My goal is to find a relatively efficient query to find all employers with no employees. The only way I could think to do this involves a subquery, but I suspect there should be a way to do this using a JOIN.

Here's what I originally had:

SELECT * FROM employer
    WHERE employer.id NOT IN (
         SELECT employer_id FROM employee
    );

As I couldn't find any answers online for this, my solution is in the answers below (admittedly with a good chunk of help from a coworker). I'm using postgres 9.3 and sqlalchemy 0.8.

Upvotes: 0

Views: 110

Answers (1)

limasxgoesto0
limasxgoesto0

Reputation: 4793

SELECT * FROM employer 
    LEFT OUTER JOIN employee ON employee.employer_id = employer.id 
    WHERE employee.id IS NULL;

For those not familiar with outer joins (I wasn't before this), this query will return all employer/employee pairs AND returns all employers without employees. These will be paired up with "dummy" employee rows, with all values being NULL. As a consequence, filtering out all results with employee ids will leave you with just the employers without employees.

(If anyone can give a better-worded explanation, by all means please do.)

In sqlalchemy:

session.query(Employer).outerjoin(
    Employer.id==Employee.employer_id
    ).filter(Employee.id==None)

Upvotes: 1

Related Questions