FROM clause

Here's a simplified syntax of FROM clause from SQL standard:

FROM table_reference ["," ...]

table_reference:
  table_factor | joined_table

joined_table:
    table_reference CROSS JOIN table_reference
  | table_reference [join_type] JOIN table_reference [join_specification]
  | table_reference NATURAL [join_type] JOIN table_reference

join_type:
    INNER
  | {LEFT | RIGHT | FULL} [OUTER]

join_specification:
    ON expr
  | USING "(" identifier ["," ...] ")"

Dialects mainly differ in what sort of joins they support:

BigQuery:

Does not support NATURAL JOIN.

DB2:

Does not support NATURAL JOIN.

DB2i:

Does not support NATURAL JOIN.

Additionally supports [LEFT|RIGHT] EXCEPTION JOIN

DuckDB:

Additionally supports ASOF JOIN (which supports the same syntax as NATURAL ... JOIN).

Additionally supports POSITIONAL JOIN.

Hive:

Does not support NATURAL JOIN and USING specification.

Additionally supports LEFT SEMI JOIN.

MariaDB:

Does not support FULL keyword in joins (like FULL OUTER JOIN or NATURAL FULL JOIN).

Does not support NATURAL INNER JOIN.

Additionally supports STRAIGHT_JOIN.

MySQL:

Does not support FULL keyword in joins (like FULL OUTER JOIN or NATURAL FULL JOIN).

Additionally supports STRAIGHT_JOIN.

N1QL:

Does not support FULL keyword in joins.

Does not support NATURAL JOIN and CROSS JOIN.

Does not support USING specification, instead supports:

join_specification:
    ON expr
  | ON [PRIMARY] KEY expr [FOR identifier]

PL/SQL:

Support the full standard plus apply-joins:

• CROSS APPLY
• OUTER APPLY

PostgreSQL:

Supports the full standard.

Redshift:

Supports the full standard.

SingleStoreDB:

Does not support NATURAL INNER JOIN and NATURAL FULL [OUTER] JOIN

Additionally supports STRAIGHT_JOIN.

Snowflake:

Does not support NATURAL INNER JOIN.

Spark:

Supports the full standard plus:

• [LEFT] SEMI JOIN
• [LEFT] ANTI JOIN

And NATURAL ... JOIN versions of these.

SQLite:

Supports the full standard.

TiDB:

Does not support FULL keyword in joins (like FULL OUTER JOIN or NATURAL FULL JOIN).

Additionally supports STRAIGHT_JOIN.

Transact-SQL:

Does not support NATURAL JOIN and USING specification.

Additionally supports apply-joins:

• CROSS APPLY
• OUTER APPLY

Trino:

Supports the full standard.