INNER JOIN with table aliases in SQL

Introduction

We use INNER JOIN with table aliases to combine rows from two tables based on a related column, making queries shorter and easier to read.

When you want to find matching records from two tables, like customers and their orders.

When tables have long names and you want to write shorter queries.

When you join the same table more than once and need to distinguish them.

When you want to improve query readability by using simple names.

When you want to combine data from two tables based on a common key.

Syntax

SQL

SELECT alias1.column1, alias2.column2
FROM table1 AS alias1
INNER JOIN table2 AS alias2
ON alias1.common_column = alias2.common_column;

Use AS to give a short name (alias) to tables.

The ON clause defines how the tables are related.

Examples

This query gets customer names and their order dates by joining customers and orders tables using aliases c and o.

SQL

SELECT c.name, o.order_date
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;

Here, aliases e and d are used without AS keyword to join employees and departments.

SQL

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

This example joins products and categories tables using aliases p and c to get product and category names.

SQL

SELECT p.product_name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id;

Sample Program

This creates two tables, inserts data, and selects customer names with their order dates using INNER JOIN and aliases.

SQL

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

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE
);

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 c.name, o.order_date
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY o.order_date;

Important Notes

Table aliases make queries shorter and easier to read.

INNER JOIN returns only rows where there is a match in both tables.

Always use the alias when referring to columns after aliasing tables.

Summary

INNER JOIN combines rows from two tables where keys match.

Table aliases are short names for tables to simplify queries.

Use ON to specify how tables relate when joining.