How the join engine matches rows in SQL

Introduction

Joining tables helps combine related information from different places. The join engine matches rows to find pairs that belong together.

When you want to see customer orders along with customer details.

When you need to combine employee data with their department info.

When you want to list products with their supplier names.

When you want to compare sales data from two different months.

When you want to find matching records between two lists.

Syntax

SQL

SELECT columns
FROM table1
JOIN_TYPE JOIN table2
ON table1.common_column = table2.common_column;

JOIN_TYPE can be INNER, LEFT, RIGHT, or FULL depending on what rows you want.

The ON clause tells the engine how to match rows between tables.

Examples

This finds all customers who have orders by matching their IDs.

SQL

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This shows all employees and their departments. If an employee has no department, they still appear.

SQL

SELECT *
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.dept_id;

This shows all suppliers and their products. Suppliers without products still appear.

SQL

SELECT *
FROM products
RIGHT JOIN suppliers
ON products.supplier_id = suppliers.supplier_id;

Sample Program

This example creates two tables: customers and orders. It then finds which products each customer bought by matching customer IDs.

SQL

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

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  product VARCHAR(50)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (101, 1, 'Book'), (102, 2, 'Pen'), (103, 1, 'Notebook');

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

Important Notes

The join engine looks at each row in the first table and tries to find matching rows in the second table based on the ON condition.

For INNER JOIN, only rows with matches in both tables appear.

For LEFT JOIN, all rows from the first table appear, even if no match is found in the second table.

Summary

Joins combine rows from two tables by matching values in specified columns.

The join engine uses the ON condition to find matching pairs of rows.

Different join types control which rows appear when matches are missing.