> For the complete documentation index, see [llms.txt](https://docs.objectiphy.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.objectiphy.net/query-builder/joins.md).

# Joins

You can use custom joins to narrow down which records are returned, even for relationships that are not defined in your mapping definitions.

The query builder can also be used to specify custom joins with aliases that can be used in the criteria. Normally, joins are handled automatically based on the relationships you define between your entities, however, sometimes you will want to limit the records that are returned by adding a custom join (perhaps back to the same entity), and specifying criteria based on any joined records. To do this, just call the `leftJoin` or `innerJoin` method on the query builder like this:

```php
$query = QueryBuilder::create()
    ->leftJoin(Policy::class, 'p2')
    ->on('p2.policyNo', '=', 'policyNo')
        ->and('p2.id', '>', 'id')
        ->and('p2.status', '=', Policy::STATUS_INFORCE)
        ->and('p2.modification', '=', 'CANCELLED')
    ->where('modification', QB::NOT_EQ, 'CANCELLED')
    ->and('p2.id', 'IS', null)
    ->buildSelectQuery();
```

The above would be translated into SQL such as this:

```
SELECT [...] FROM `policy` 
LEFT JOIN `policy` `p2` ON `p2`.`policy_no` = `policy`.`policy_no`
    AND `p2`.`id` > `policy`.`id`
    AND `p2`.`status` = 'INFORCE'
    AND `p2`.`modification` = 'CANCELLED' 
WHERE `policy`.`modification` != 'CANCELLED'
    AND `p2`.`id` IS NULL 
```