# Join hints
<a name="join-hints"></a>

Join hints suggest join strategies for query execution. The syntax, arguments, and some examples come from the [Apache Spark SQL Reference](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html#join-hints) for more information

### BROADCAST
<a name="broadcast-hint"></a>

Suggests that AWS Clean Rooms use broadcast join. The join side with the hint will be broadcast regardless of autoBroadcastJoinThreshold. If both sides of the join have the broadcast hints, the one with the smaller size (based on stats) will be broadcast.

*Aliases:* BROADCASTJOIN, MAPJOIN

*Parameters:* Table identifiers (optional)

*Examples:*

```
-- Broadcast a specific table
SELECT /*+ BROADCAST(students) */ e.name, s.course
FROM employees e JOIN students s ON e.id = s.id;

-- Broadcast multiple tables
SELECT /*+ BROADCASTJOIN(s, d) */ *
FROM employees e
JOIN students s ON e.id = s.id
JOIN departments d ON e.dept_id = d.id;
```

### MERGE
<a name="merge-hint"></a>

Suggests that AWS Clean Rooms use shuffle sort merge join.

*Aliases:* SHUFFLE\$1MERGE, MERGEJOIN

*Parameters:* Table identifiers (optional)

*Examples:*

```
-- Use merge join for a specific table
SELECT /*+ MERGE(employees) */ *
FROM employees e JOIN students s ON e.id = s.id;

-- Use merge join for multiple tables
SELECT /*+ MERGEJOIN(e, s, d) */ *
FROM employees e
JOIN students s ON e.id = s.id
JOIN departments d ON e.dept_id = d.id;
```

### SHUFFLE\$1HASH
<a name="shuffle-hash-hint"></a>

Suggests that AWS Clean Rooms use shuffle hash join. If both sides have the shuffle hash hints, the query optimizer chooses the smaller side (based on stats) as the build side.

*Parameters:* Table identifiers (optional)

*Examples:*

```
-- Use shuffle hash join
SELECT /*+ SHUFFLE_HASH(students) */ *
FROM employees e JOIN students s ON e.id = s.id;
```

### SHUFFLE\$1REPLICATE\$1NL
<a name="shuffle-replicate-nl-hint"></a>

Suggests that AWS Clean Rooms use shuffle-and-replicate nested loop join.

*Parameters:* Table identifiers (optional)

*Examples:*

```
-- Use shuffle-replicate nested loop join
SELECT /*+ SHUFFLE_REPLICATE_NL(students) */ *
FROM employees e JOIN students s ON e.id = s.id;
```

### Troubleshooting Hints in Spark SQL
<a name="join-hint-warning-cases"></a>

The following table shows common scenarios where hints are not applied in SparkSQL. For additional information, see [Considerations and limitations](sql-commands-hints-spark.md#hints-usage-notes).

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/join-hints.html)