What's the purpose of SQL keyword "AS"?

Unpacking "AS"

Stepping into SQL from other programming languages may raise questions such as "What's this AS doing?" It's like a translator for your SQL expressions, hinting at what each element signifies.

• Clarity: It specifies aliases, making complex SQL views and joins less puzzling.
• Simplicity: It provides a way to give friendly names to entities used in your SQL Statements.
• Compatibility: AS keeps you aligned with modern RDBMS syntax, reducing cross-database issues.

Visualization

Picture a busy marketplace where everyone uses name tags:

Before 'AS': [Stall1, Stall2, Stall3]

Using 'AS':
| Original Name (Stall) | AS Keyword | Alias (Tag)  |
| --------------------- | ---------- | -----------  |
| Stall1                | AS         | Apples       |
| Stall2                | AS         | Bananas      |
| Stall3                | AS         | Oranges      |

There AS goes, tagging your columns and making them more memorable for everyone. 🏷️🎉

Origin and Best Practices

AS has had a mixed reception in SQL's timeline. Earlier practices didn't always use AS to designate aliases, while modern conventions have adopted it for explicability and consistency.

Although using AS is largely a matter of preference, many prefer to use it in these situations:

• Long or complex queries
• JOIN operations to clarify aliasing
• Renaming entities to ensure unambiguous interpretation

However, clear column/table names or natural joins can sometimes eliminate the necessity for AS.

Deep Dive into "AS"

Cleaning up self-joins and sub-queries

When dealing with self-joins or complicated sub-queries, AS helps reduce duplications and confusions. Here's how AS can make your code clearer rather than scarier:

SELECT 
  em.name AS employee_name,  -- Even SQL wishes to know itself better
  mgr.name AS manager_name   -- Same names? Not on my watch!
FROM 
  employees em
JOIN employees mgr ON em.manager_id = mgr.employee_id;


Preventing ambiguity in dot-notation

Ever got confused with multiple columns having the same names in a single query? SQL did, too. Here's where AS steps in:

SELECT 
  orders.order_id,
  customers.name AS customer_name, -- Whose name do we need? Oh! A customer's
  employees.name AS employee_name  -- Otherwise, SQL will radically #CancelColumnNames
FROM 
  orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN employees ON orders.employee_id = employees.employee_id;


Ensuring cross-compatibility

As SQL evolved, the inclusion of AS got more endorsement due to interoperability. For instance, Oracle initially did not support AS for table aliasing, which was only introduced in later SQL standards. Using AS maintains cross-database compatibility.