## Syntax
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
stmt_start((START))
stmt_start --> stmt_0_0[JOIN_SET]:::quoted
stmt_0_0 --> stmt_0_1["("]:::quoted
stmt_0_1 --> stmt_0_2[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>]
stmt_0_2 --> stmt_0_7
stmt_0_2 --> stmt_0_3[","]:::quoted
stmt_0_3 --> stmt_0_4[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>]
stmt_0_4 --> stmt_0_7
stmt_0_4 --> stmt_0_5[","]:::quoted
stmt_0_5 --> stmt_0_6[numericConstant]
stmt_0_6 --> stmt_0_7[")"]:::quoted
stmt_0_7 --> stmt_end((END))
```
## Purpose
The `join_set` hint controls the join approach between two data sources. A column value `IN` lookup will be used instead of a full table scan when the number of rows on the left-hand side does not exceed the maximum number of rows specified in the hint. When not specified, a column value `IN` lookup will only be used when the number of rows on the left-side does not exceed 5.000 rows.
The actual implementation of a hash lookup depends on the platform on which the data container executes:
- On OData, a number of requests will be made using an `in`-construct with a limited number of in-values.
- On a relational database platform, a native SQL `in` will be used.
The first identifier is the alias of the table on the right-hand side of the join. The second identifier is the name of the column used to join upon in the right-hand side. The numeric constant specifies up to what number of rows on the left-hand side of the join will allow the join set hint to be used. A full table join is made when the number of rows on the left-hand side exceeds the numeric constant.
## Examples
The following example takes for instances 5.000 sales invoices from an Exact Online environment with 100.000 sales invoices. Each sales invoice has between 4 and 10 lines. The join does not retrieve all sales invoices nor all invoice lines, but instead fetches the 5.000 sales invoices using the where-clause, and then retrieves the related invoice lines using a column value `IN` lookup by `InvoiceId`. Since Exact Online is an OData source, the approximately 30.000 invoice lines will be retrieves in 300 session I/Os each having an in-construct for 100 lines on `InvoiceId`.
```sql
select /*+ join_set(sil, invoiceid, 10000) */ *
from ExactOnlineREST..SalesInvoices sik
join ExactOnlineREST..SalesInvoiceLines sil
on sil.invoiceid = sik.invoiceid
where sik.status = 50
and sik.InvoiceDate >= to_date( :P_RECEIPT_DATE_FROM, 'YYYYMMDD')
and sik.InvoiceDate <= to_date( :P_RECEIPT_DATE_TO, 'YYYYMMDD')
```