Primary keys and foreign keys in SQL Server

A primary key uniquely identifies a row (customer_id). A foreign key says this value must exist over there (Orders.customer_id → Customers). The JOIN you write follows that line.

The line you already use

SELECT o.order_id, c.first_name, c.last_name
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id;

If a foreign key exists, an order cannot be saved for a customer that is not there. If it does not exist, you will find orphan orders with a LEFT JOIN and NULL names.