SQL Aliases
In SQL, an alias is a way
to assign a temporary or alternate name to a table or column in a query result.
Aliases are commonly used to make the output of a query more meaningful, to
differentiate between columns with similar names, or to simplify complex
queries with lengthy table or column names.
In other words
1.
SQL aliases are used to give a table, or a column in a
table, a temporary name.
2.
Aliases are often used to make column names more
readable.
3.
An alias only exists for the duration of that query.
4.
An alias is created with the AS keyword.
Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;
Aliases
can be useful when:
- There are more than one table involved in a
query
- Functions are used in the query
- Column names are big or not very readable
- Two or more columns are combined together
Aliases
can be used in
the SELECT, FROM, and JOIN
clauses of
a SQL query. Here is the syntax for using aliases:
1. Alias for a table in the FROM clause:
SELECT alias.column_name1, alias.column_name2, ...
FROM table_name AS alias;
Where alias is the alias name that you want to assign to the table_name.
2. Alias for a column in the SELECT clause:
SELECT column_name AS alias
FROM table_name;
Where alias is the alias name that you want to assign to the column_name.
3. Alias for a column in the JOIN clause:
SELECT column_name1, column_name2, ...
FROM table_name1
JOIN table_name2 ON table_name1.column_name = table_name2.column_name
In this case, table_name1 and table_name2 are table aliases, and column_name is a column alias that you can use to refer to columns from different tables that have the same name.
Here is an example that demonstrates the use of aliases in a SQL query:
SELECT o.order_id, c.customer_name, p.product_name
FROM orders AS o
JOIN customers AS c ON
o.customer_id = c.customer_id
JOIN products AS p ON o.product_id = p.product_id
In this example, o,
c, and p are table aliases for the orders, customers, and products tables, respectively, and order_id, customer_name, and
product_name are column aliases for the corresponding columns in
the query result.
The use of aliases makes the query more concise and readable.
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT CustomerID AS ID,
CustomerName AS Customer
FROM Customers;
The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: It requires double quotation marks or square brackets if the alias name contains spaces:
Example
SELECT CustomerName AS Customer,
ContactName AS [Contact Person]
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
Example
SELECT CustomerName, Address +
', ' + PostalCode + '
' + City + ', '
+ Country AS Address
FROM Customers;
Note: To
get the SQL statement above to work in MySQL use the following:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS
Address
FROM Customers;
Note: To get the SQL statement above to work in Oracle use the following:
SELECT CustomerName, (Address ||
', ' || PostalCode || '
' || City || ', '
|| Country) AS Address
FROM Customers;
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c,
Orders AS
o
WHERE c.CustomerName='Around the Horn' AND
c.CustomerID=o.CustomerID;
The following SQL statement is the same as above, but without aliases:
Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
0 Comments