Exception Join in Zend\Db 2.8.2 (DB2)

Question

I can't seem to figure out how to create an exception join using Zend\Db?

I see explicit support for inner, outer, left, right, right outer and left outer. But was hoping there is still a way to retain the abstraction.

Is the only way to use:

$db->query('Select * from...exception join....')

Answer 1

You can simulate an exception join with an outer join where the rows on the right side of the join are missing. For example:

select a.id, a.description, b.name
  from table1 a
    left exception join table2 b 
      on a.id = b.id

is equivalent to:

select a.id, a.description, b.name
  from table1 a
    left outer join table2 b 
      on a.id = b.id
  where b.id is null

I can't test this right now, but based on the documentation, you should be able to do something like this:

$select = new Select();
$select->columns(array('id', 'description'));
$select->from(array('a' => 'table1'));
$select->join(
    array('b' => 'table2'),
    'b.id = a.id',
    array('name'),
    $select::OUTER
);
$select->where(array('b.id' => null));