How are relationships between tables specified via identifiers in SQL when performing a JOIN?

In SQL, to specify relationships between tables during a JOIN, a join condition based on identifiers (keys) is used in the ON clause. Usually, this is the primary key of one table and the foreign key of the other.

Example:

SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Here, the orders and customers tables are linked via the customer_id field in orders and the id in customers. This allows retrieving data from both tables, combined based on matching identifiers.