Many common joins are between two tables related by a foreign key. The most common join restricts foreign key values to be equal to primary key values. The example you have already seen restricts foreign key values in the sales_order table to be equal to the candidate key values in the employee table.
SELECT emp_lname, id, order_date FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id
The query can be more simply expressed using a KEY JOIN.
