Advanced Join Techniques: LATERAL Joins, Semi Joins, Anti Joins
LATERAL joins let a subquery in the FROM clause reference columns from earlier in the same FROM clause. Semi joins return rows where a match exists in another table, without duplicating those rows. Anti joins return rows where no match exists.