Exclude rows with no associated row in a joined table

Question

I would like to get instances (rows) of table foo that do not have a reference in bar

Table foo:

+----+-----+
| id | baz |
+----+-----+
|  1 |  23 |
|  2 |  56 |
|  3 |  45 |
|  4 |  78 |
+----+-----+

table bar:

+-----+--------+-----+
| id  | foo_id | zab |
+-----+--------+-----+
|  7  |      2 | s1  |
|  8  |      4 | s2  |
+-----+--------+-----+

The result of my query should be instances of foo as below:

+----+-----+
| id | baz |
+----+-----+
|  1 |  23 |
|  3 |  45 |
+----+-----+

Using the SQLAlchemy ORM, I have tried join and outerjoin, but the solution is still escaping me. Something tells me that the solution is easy and right in front of my face...

q = db.session.query(Foo).join(Baz, Baz.foo_id == Foo.id)

q = db.session.query(Foo).outerjoin(Baz, Baz.foo_id == Foo.id)

Answer 1

The SQL query you're looking for is this:

SELECT foo.* FROM foo LEFT JOIN bar ON bar.foo_id = foo.id WHERE bar.foo_id IS NULL;

LEFT JOIN, as opposed to INNER JOIN, includes all rows from the 'left' table (i.e. the table specified in FROM tblname), even if they have no associated row in the 'right' table (the table specified in JOIN tblname). This means that rows that have no associated row in the right table will have NULL values instead:

foo.id | foo.baz | bar.id | bar.foo_id | bar.zab
-------+---------+--------+------------+--------
     1 |      23 |   NULL |       NULL |    NULL
     2 |      56 |      7 |          2 |      s1

So, filter for those rows that have a NULL in the right table's primary key (which can't be null in any other case, whereas other columns from the right table could be), and you get foo rows that have no associated bar.

In SQLAlchemy, that becomes this:

q = db.session.query(Foo).join(Bar, isouter=True).filter(Bar.id == None)

The isouter=True flag to join is how you do a LEFT JOIN with SQLAlchemy.