Joining on primary key to foreign key in SQL

Introduction

Joining on primary key to foreign key helps combine related data from two tables. It connects main records with their linked details.

You want to see customer orders along with customer details.

You need to list products with their supplier information.

You want to combine employee data with their department info.

You want to show students with their enrolled courses.

You want to get invoice details along with the client information.

Syntax

SQL

SELECT columns
FROM table1
JOIN table2 ON table1.primary_key = table2.foreign_key;

The primary key is a unique identifier in the main table.

The foreign key in the second table points to the primary key.

Examples

Join customers with orders using customer_id as the link.

SQL

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

Join employees with their departments using department_id.

SQL

SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

Join products with suppliers using supplier_id.

SQL

SELECT products.product_name, suppliers.supplier_name
FROM products
JOIN suppliers ON products.supplier_id = suppliers.supplier_id;

Sample Program

This example creates two tables: customers and orders. It inserts sample data and joins them on customer_id to show each order with the customer's name.

SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (101, 1, '2024-01-10'), (102, 2, '2024-01-11'), (103, 1, '2024-01-12');

SELECT customers.name, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY orders.order_date;

Important Notes

Joining on primary key to foreign key is usually fast because keys are indexed.

Make sure foreign key values exist in the primary key table to avoid missing matches.

Use INNER JOIN to get only matching rows, LEFT JOIN if you want all from primary key table even without matches.

Summary

Joining on primary key to foreign key connects related data from two tables.

Primary key uniquely identifies records; foreign key points to it.

This join helps combine main records with their linked details easily.