MySQL Aliases

Give temporary names to tables and columns

🏷️ What are Aliases?

Aliases are temporary names given to tables or columns for the duration of a query. They make your SQL more readable and are essential when working with joins or complex queries. 


-- Column alias example
SELECT name AS customer_name, email AS contact_email
FROM customers;

Output:

customer_name contact_email
John Doe [email protected]
Jane Smith [email protected]

Key Alias Concepts

📊

Column Aliases

Rename columns in results 

SELECT price AS cost FROM products
📋

Table Aliases

Shorten table names 

SELECT c.name FROM customers AS c
🔤

AS Keyword

Optional but recommended 

SELECT name AS full_name
💡

Spaces in Aliases

Use quotes for spaces 

SELECT price AS "Unit Price"

🔹 Column Aliases

Column aliases rename columns in your query results, making them more descriptive and user-friendly. They're particularly useful when working with calculated fields, aggregate functions, or when you want to present data with clearer column headers. The AS keyword is optional but improves readability. 

-- Using column aliases for clarity
SELECT 
    product_id AS id,
    product_name AS name,
    price * 1.2 AS price_with_tax,
    stock_quantity AS "Items Available"
FROM products;

Output:

id name price_with_tax Items Available
1 Laptop 1200.00 15
2 Mouse 36.00 50

🔹 Table Aliases

Table aliases provide shorter names for tables, making queries more concise and easier to write. They're essential when working with joins where you need to reference multiple tables. Table aliases help avoid ambiguity when columns have the same name in different tables and reduce typing in complex queries. 

-- Using table aliases to simplify queries
SELECT 
    c.customer_id,
    c.name,
    o.order_date,
    o.total_amount
FROM customers AS c, orders AS o
WHERE c.customer_id = o.customer_id;

Output:

customer_id name order_date total_amount
1 Alice Brown 2024-01-15 250.00
2 Bob Wilson 2024-01-20 180.50

🔹 Aliases with Functions

When using aggregate functions or calculations, aliases make your results much more readable and professional. Without aliases, function results often have generic or confusing column names. Aliases transform these into meaningful, descriptive headers that clearly communicate what the data represents to anyone reading your query results. 

-- Using aliases with aggregate functions
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MAX(amount) AS highest_order
FROM orders;

Output:

total_orders total_revenue average_order_value highest_order
150 45000.00 300.00 1500.00

🔹 Aliases with CONCAT

Combining multiple columns into one using CONCAT is a common task, and aliases make the result column name meaningful. This technique is perfect for creating full names from first and last names, complete addresses, or any combined data. The alias gives your concatenated result a proper, descriptive column name. 

-- Combining columns with aliases
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT(city, ', ', country) AS location,
    email
FROM employees;

Output:

full_name location email
John Smith New York, USA [email protected]
Sarah Johnson London, UK [email protected]

🧠 Test Your Knowledge

Is the AS keyword required when creating aliases?