Three less-common SQL patterns—LATERAL, semi joins, and anti joins—are presented as cleaner fixes for row-by-row subqueries, existence checks, and no-match queries that INNER and LEFT JOINs handle awkwardly.
LATERAL is highlighted for set-returning functions in the FROM clause, where subqueries must reference earlier tables; Rosidi shows PostgreSQL regexp_matches counting 3 "bull" and 2 "bear" occurrences without matching substrings.
Semi joins are framed as a way to return each left-table row once when any match exists, typically with EXISTS or IN; the customer example finds users with at least 1 order above $100 without INNER JOIN duplication.
Anti joins invert that logic with NOT EXISTS or LEFT JOIN ... IS NULL; the article stresses keeping date filters in the ON clause, yielding free users with no April 2020 calls such as 1575 and 1910.
Rosidi’s takeaway is a short rule set: use LATERAL for correlated FROM subqueries, EXISTS when INNER JOIN overcounts, and NOT EXISTS or NULL-checked LEFT JOINs for unmatched rows.