Another lesser known JOIN - the natural join. But maybe the NATURAL JOIN is not as obscure after all, since it has its own keyword, at least in a couple of SQL dialects - see PostgreSQL portrayed below (sorry, it's not supported in BigQuery, but it does recognize it).
So what's special about it? Well, it joins the tables based on columns that have the same name (and datatype) in the two tables. That is, we don't need to specify any join conditions.
Watch out because if there are no columns with the same name and datatype, it defaults to a Cartesian product is produced (which is what CROSS JOIN does).
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.