🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

What Is the SQL AS Clause? Make Your Queries Dramatically More Readable with Aliases!

"The column name for my calculation looks ugly, like `price * 1.1`..."
"After a JOIN, the table names are so long that my query is a mess to read!"

As you start writing SQL, have you ever felt these "little frustrations"? The query works, but it's just hard to read. When you look back at it later, it takes time even for you to figure out what you were doing...

The magic keyword that solves these problems in an instant is the "AS" clause, which we'll introduce today. Using AS, you can give "aliases (nicknames)" to tables and columns. It's like calling a friend by a nickname instead of their long, full name. Once you master AS, your SQL queries will become dramatically easier to read and write!

In this article, we'll thoroughly explain everything from the basics of the AS clause, which skyrockets SQL readability, to advanced techniques like using it with JOINs and self-joins, all with copy-paste-ready sample code.

Preparation: A Practice Table

First, let's set up a table to experience the convenience of aliases. This time, we'll use a simple products table. 


-- Drop table if it exists
DROP TABLE IF EXISTS products;

-- Create products table
CREATE TABLE products (
    id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price INT,
    category VARCHAR(50)
);

-- Insert data
INSERT INTO products (id, product_name, price, category) VALUES
(1, '高機能オフィスチェア', 35000, '家具'),
(2, '静音ワイヤレスキーボード', 8000, '周辺機器'),
(3, '4K対応27インチモニター', 42000, '周辺機器'),
(4, '電動昇降式デスク', 65000, '家具'),
(5, 'ノイズキャンセリングヘッドホン', 28000, 'オーディオ');

Let's explore the various ways to use the AS clause with this table!

1. Column Aliases: Tidying Up Your Results

A column alias is a feature that renames the column names (headers) in a query's result set to something more understandable. Don't worry, it doesn't change the actual column names in the original table. It's mainly useful when generating reports or displaying data in an application.

Example 1: Renaming Columns to Be More Understandable

It's useful when you want to display English column names like `product_name` and `price` as more descriptive headers like "Product Name" and "Price". 


SELECT
    product_name AS "Product Name",
    price AS "Price"
FROM
    products;

Just like that, the result headers have changed simply by specifying a new name after AS. Now you can create reports that are easy to understand, even for people who don't know SQL.

Example 2: Naming Calculated Columns

One of the most common uses for AS is to give a clear name to a column that results from a calculation or function. For example, let's calculate a price including tax (price × 1.1). 


-- Without AS, the column name just becomes the calculation
SELECT
    product_name,
    price * 1.1
FROM
    products;

The column name ends up as `price * 1.1`, which looks pretty clunky, right? This is where AS comes to the rescue. 


-- Using AS to give it a clear name
SELECT
    product_name AS "Product Name",
    price * 1.1 AS "Price with Tax"
FROM
    products;

How about that? Just by adding AS "Price with Tax", the result is much easier to read. It's standard practice to use AS to name the results of aggregate functions (`COUNT`, `SUM`, `AVG`, etc.) in the same way.

※ By the way, in many SQL environments, the AS keyword is optional for column aliases (e.g., `price "Price"`). However, to improve code readability, explicitly writing `AS` is recommended.

2. Table Aliases: Simplifying Complex Queries

A table alias is a feature that gives a table a "temporary, short name" within a query. This is incredibly effective, especially when you're using JOIN with multiple tables. It makes queries shorter, simpler, and makes it obvious which table a column belongs to.

Example 1: Cleaning Up a JOIN Statement

Let's look at an example using an "employees" and "departments" table. 


-- Table setup for this example
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (id INT, department_name STRING);
INSERT INTO departments VALUES (1, '営業部'), (2, '開発部');
CREATE TABLE employees (id INT, name STRING, department_id INT);
INSERT INTO employees VALUES (1, '山田 太郎', 1), (2, '鈴木 花子', 2);

Without aliases, you have to write out the full `table_name.column_name` format every time, which is very verbose.


-- Without aliases (verbose example)
SELECT
    employees.name,
    departments.department_name
FROM
    employees
INNER JOIN
    departments ON employees.department_id = departments.id;

Let's rewrite this using table aliases. We'll name employees as e and departments as d. 


-- With aliases (recommended way)
SELECT
    e.name,
    d.department_name
FROM
    employees AS e
INNER JOIN
    departments AS d ON e.department_id = d.id;

It's amazingly clean now! It's clear that e.name means the `name` column from the `employees` table, and the overall readability of the query has improved dramatically.

Practice! Let's Try Aliases in Your Browser

Thanks for waiting! Here is an environment where you can get hands-on and try out the alias techniques you've learned so far.

Copy the entire code block below, save it as a file named something like alias_practice.html, and open it in your browser. Try applying aliases to the `INNER JOIN` query to experience how much more readable it becomes! 


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>SQL Alias Playground</title>
    <script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>
    <style>
        body { font-family: sans-serif; padding: 2rem; background-color: #f9f9f9; }
        .container { max-width: 800px; margin: auto; background: white; padding: 2rem; border-radius: 8px; box-shadow: 0 4px 8px rgba(0,0,0,0.1); }
        h1 { color: #333; }
        textarea { width: 100%; height: 180px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
        button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
        button:hover { background-color: #0056b3; }
        #result-area { margin-top: 2rem; }
        table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .error { color: red; font-weight: bold; }
    </style>
</head>
<body>
    <div class="container">
        <h1>Let's Try Aliases!</h1>
        <p>Enter an SQL statement in the text area below and press the "Execute SQL" button.</p>
        <textarea id="sql-input">-- Write your SQL in here
SELECT
    e.name AS "Employee Name",
    d.department_name AS "Department Name"
FROM
    employees AS e
INNER JOIN
    departments AS d ON e.department_id = d.id;</textarea>
        <button onclick="executeSQL()">Execute SQL</button>
        <div id="result-area"></div>
    </div>

    <script>
        // Initialize database and prepare data for JOIN
        const db = new alasql.Database();
        db.exec(`
            CREATE TABLE departments (id INT, department_name STRING);
            INSERT INTO departments VALUES (1, '営業部'), (2, '開発部');
            CREATE TABLE employees (id INT, name STRING, department_id INT);
            INSERT INTO employees VALUES (1, '山田 太郎', 1), (2, '鈴木 花子', 2);
        `);

        function executeSQL() {
            const sql = document.getElementById('sql-input').value;
            const resultArea = document.getElementById('result-area');
            resultArea.innerHTML = '';

            try {
                const result = db.exec(sql);
                if (result.length > 0) {
                    resultArea.appendChild(createTable(result));
                } else {
                    resultArea.innerHTML = '<p>The query returned 0 rows.</p>';
                }
            } catch (e) {
                resultArea.innerHTML = `<p class="error">Error: ${e.message}</p>`;
            }
        }

        function createTable(data) {
            const table = document.createElement('table');
            const thead = table.createTHead();
            const tbody = table.createTBody();
            const headerRow = thead.insertRow();
            for (const key in data[0]) {
                const th = document.createElement('th');
                th.textContent = key;
                headerRow.appendChild(th);
            }
            data.forEach(rowData => {
                const row = tbody.insertRow();
                for (const key in rowData) {
                    const cell = row.insertCell();
                    cell.textContent = rowData[key];
                }
            });
            return table;
        }

        // Initial display
        executeSQL();
    </script>
</body>
</html>

Points to Watch Out For: Alias Scope and Order of Operations

Aliases are very convenient, but there's one important rule you need to know. It has to do with the "SQL order of execution."

You Can't Use Column Aliases in `WHERE` or `GROUP BY` Clauses

The internal order in which SQL executes is a bit different from the order we write it in (SELECT → FROM → WHERE). Broadly speaking, the actual order is as follows:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Did you notice? The SELECT clause is processed after the WHERE and GROUP BY clauses. Because of this, you cannot use a column alias defined in the SELECT list in a clause that is processed before it, like WHERE. 


-- This example will cause an error
-- This example uses the 'products' table for its explanation
SELECT
    product_name AS "Product Name"
FROM
    products
WHERE
    "Product Name" = '高機能オフィスチェア';

The query above will result in an error. This is because at the time the WHERE clause is processed, the alias "Product Name" does not yet exist. In this case, you must use the original column name. (To test this error, you would need to change the data setup in the practice area above back to the 'products' table). 


-- This is the correct way
SELECT
    product_name AS "Product Name"
FROM
    products
WHERE
    product_name = '高機能オフィスチェア';

On the other hand, you can use column aliases in the ORDER BY clause, since it's processed after the SELECT clause. This is very useful, so it's a good one to remember.

Summary

Great work! In this article, we've explained the AS clause (alias), which makes SQL queries dramatically easier to read and write.

AS is an essential keyword that everyone should use, from beginners just starting with SQL to professionals handling complex data. Starting today, try giving your queries some "nicknames". Your future self, and your teammates, will surely thank you for it!

Related Templates