Tables can be joined using join conditions. A join condition is simply a search condition. It chooses a subset of rows from the joined tables based on the relationship between values in the columns. For example, the following query retrieves data from the Products and SalesOrderItems tables.

SELECT *
FROM Products JOIN SalesOrderItems
   ON Products.ID = SalesOrderItems.ProductID;

The join condition in this query is

Products.ID = SalesOrderItems.ProductID

This join condition means that rows can be combined in the result set only if they have the same product ID in both tables.

Join conditions can be explicit or generated. An explicit join condition is a join condition that is put in an ON clause or a WHERE clause. The following query uses an ON clause. It produces a cross product of the two tables (all combinations of rows), but with rows excluded if the ID numbers do not match. The result is a list of customers with details of their orders.

SELECT *
FROM Customers 
JOIN SalesOrders
ON SalesOrders.CustomerID = Customers.ID;

A generated join condition is a join condition that is automatically created when you specify KEY JOIN or NATURAL JOIN. For key joins, the generated join condition is based on the foreign key relationships between the tables. For natural joins, the generated join condition is based on columns that have the same name.

When you use an ON clause with a key join or natural join, the join condition that is used is the conjunction of the explicitly specified join condition with the generated join condition. This means that the join conditions are combined with the keyword AND.