Renaming Table Name and Column Name using Alias Keyword

The SQL Aliases are used to temporarily rename a column name or a table name with user friendly heading.

The SQL aliases are created to make column or table names more readable.

The use of aliases means to rename a table or column in a particular SQL statement will not affect to that database table or column.

In ORDER BY and GROUP BY clauses, a column's name can be used to relate to the column's value, but not in WHERE or HAVING clauses; you should write out the phrase alternatively.

Aliases are widely used to make your result set's column titles easier to read. When utilising an aggregate function like MIN, MAX, AVG, SUM, or COUNT in your query, you'll most likely alias a column.

Syntax:

SELECT 
col_name [AS alias_col_name], aggregate_function(col_name) [AS alias_name], ...
FROM table_name [ alias_table_name ];

There is no requirement for the AS keyword in column name and table name alias table name.

Example 1:

Let's look at an example of how to use to alias a column name in SQL.

In this example, we have a table called employees with the following data:

Let's demonstrate how to alias a column. Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

In this example, we've aliased the COUNT(*) field as total. As a result, total will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

Example 2:

SELECT name AS Employee_Name, 
SUM(hours) AS Total_Hours 
FROM employee_hour emp_hours
GROUP BY name;

Output:

Multiple columns can be aliased in the same query.

Long table names and needless column identity (e.g., the table may have two IDs, but only one is used in the statement) can be eliminated, reduce the length of�code. This, together with table aliases, allows you to use longer meaningful names in your database structure while still maintaining queries on it simple.

In the SELECT clause of a SELECT statement, a column alias appears after the column name. For column heads, the DBMS will utilise default values. (The original column's name in the table description is used as the default heading in a result.) The AS clause can be used to create a column alias.

A column alias is a different name (identifier) that you supply to modify how column heads appear in a result. If column names are confusing, difficult to type, or too lengthy or short, use column aliases.

Column aliases are mostly used to keep the code shorter and the column names easier to understand.

Note: If the alias includes spaces, punctuation, or special characters, it's recommended that you surround it in single or double quotes. If the alias is a single non-reserved term with only letters, numerals, or underscores, you can skip the quotations. If you want a column to keep its default heading, remove the AS clause from it.

Syntax: To create column aliases:

SELECT column1 [AS] alias1,
column2 [AS] alias2,
...
columnN [AS] aliasN
FROM table;

column1, column2, ..., columnN are column names; alias1, alias2, ..., aliasN are their corresponding column aliases; and table is the name of the table that contains column1, column2, ....

Example 1:

SELECT course_id AS "Course ID", exercise_id AS "Exercise ID"
FROM bugs;

Example 2: Aliases can be created in all versions of SQL using double quotes (").

SELECT Name AS "First Name", 
MName AS "Middle Name",
LName AS "Last Name"
FROM Employees  

Example 1: Is it possible to alias a column name and then use that in a CASE statement?

SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table;

I am trying to alias the column because actually my CASE statement would be generated programmatically, and I want the column that the case statement uses to be specified in the SQL instead of having to pass another parameter to the program.

It is possible to utilise column_alias in an ORDER BY clause, though not in a WHERE, GROUP BY, or HAVING clause.

In standard SQL, you can't use a WHERE clause to refer to a column alias. This constraint is in place since this column value may not yet be known when the programme is run.

Syntax:

select col1 as alias1
FROM table1
WHERE col2 in (select col3 from table2 where cil3 = alias1);

Example 1: use of correlated column aliases in the WHERE clause:

select abs(col1) as alias1
FROM table1
WHERE col2 in (select col3 as a3 from table2 where alias3 = alias1);

Example 2:

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp
WHERE YEAR=2010

You get an error

It is because the column alias are not immediately known to the WHERE clause, whereas it is known in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.

Example 3:

SELECT logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

If the alias name includes spaces, it must be surrounded by quotation marks.

While aliasing a column name, spaces are permitted. Therefore, it is not recommended to use spaces when aliasing a table name.

The alias name can only be used inside the SQL statement.

Example 1: using space in alias Double quotes for alias with a space.

SELECT U.first_name + ' ' + U.last_name AS "User Name"

Example 2:

SELECT dept_id, COUNT(*) AS "Count_employees"
FROM employees
GROUP BY dept_id;

Output:

We've aliased the COUNT(*) field to "Count employees" in this instance, so that this will be the heading for the second column in our result set. "Count employees" must be surrounded in quotes in the SQL statement since this col alias name contains spaces.

In SQL, the Delete statement is used to remove one or more records from a table. By combining the DELETE statement with the WHERE clause, you can delete specific rows from a table; otherwise, it would delete all entries.

Example : The reason why needs to use aliases is that some conditions need to be written in the subquery in the where condition of delete, for example:

delete products from products as p1
where p1.rowid<
(
    select MAX(p2.rowid) from products p2 
    where p1.name=p2.name and p1.price=p2.price
)

result:

If you move a database used by an application to a new server and you can't change the specified database connection in the application, you can use an alias and keep the application configuration unchanged.

SQL Relations Subqueries are used to retrieve data from a table that is mentioned in the outer query. Because the subquery is associated with the outer query, it is called correlated. A table alias (also known as a correlation name) must be provided in these queries to identify which table link to utilise.

Because you have access to the parent's aliases in a correlated subquery, the aliases have to be identical in both the parent query and the corresponding subquery.

Example 1: The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables.

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');

Output:

Example 2: If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:

select a.foo
,b.bar
from Foobar a
join Bar b
on b.FooBarID = a.FooBarID
where not exists
(select 1
from Bar b2
where b2.BarCategoryID = b.BarCategoryID
and b2.BarDate > b.BarDate)

The correlated subquery does not have an alias as it does not participate in a join as such. The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

Example 3:

SELECT Inc.OwnerIdName as OIN
,(SELECT COUNT(*)
FROM Incident 
WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesOpened
,(SELECT COUNT(*)
FROM IncidentResolution 
WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesClosed
FROM Incident Inc

If you want to use the Alias directly, you will have to do that early on in your query and then use it for all the Scalar Sub-Queries.

SQL aliases are used to provide a temporary name to a table or a column in a table. Aliases are frequently used to enhance column names easier to understand. An alias persists only while the query is active. The AS keyword is used to generate an alias.

TABLE ALIASES are used to make your SQL more readable by shortening it or when conducting a self join (ie: listing the same table more than once in the FROM clause).

Table Alias assistance is a highly handy addition to the Select statement. Table aliasing is a simple technique to make writing your select statement simpler, and it also has the added benefit of allowing you to quickly alter the table utilised in your query.

Table aliases are a necessary evil when dealing with highly normalized schemas. For example, and I'm not the architect on this DB so bear with me, it can take 7 joins in order to get a clean and complete record back which includes a person's name, address, phone number and company affiliation.

Table aliases are typically used to retrieve data from multiple tables and link them using field relations.

Example 1: To fetch Grade and NAME of Student with Age = 20.

SELECT s.NAME, d.Grade FROM Student AS s, Student_Details
AS d WHERE s.Age=20 AND s.ROLL_NO=d.ROLL_NO; 

Output:

Example 2: Typically that would look like the statement above. Notice how you are using the combination of table and column names in multiple places throughout the statement. Now that we support Table Alias, you can make this statement in this way instead:

Select a.Address, a.AddressPostalCode, 
a.BuildingName, p.plantype, p.plannr, p.plannavn
From City As "c", Plan As "p";

Notice how much simpler it is to read the statement now that the long table names have been removed. The table and the Table Alias: City As "c" has been bolded. The string included in quotes is the Table Alias, which can be substituted for the table name in the statement. A Table Alias is a string of characters that identifies the table. I usually only use one or two characters from the table name, usually just the first.

You only need to alter the table name in the From section of the statement if you wish to utilise a different table in your statement. This, of course, necessitates that any columns used be present in both tables.

This sequence specifies which names (columns or aliases) are permitted in relational database systems since they really must be added in a prior step.

In SQL Server, implement group by for the alias column. In general, we can't utilise alias column names in query functions like where, group by, order by, and so forth. To build groups by for the alias column, we must wrap all SQL query statements in a subquery and construct groups on the statement's exterior.

Example 1: Since this GROUP BY clause is run before the SELECT clause in Oracle and SQL Server, you can't use a term defined in the SELECT clause in the GROUP BY clause.

SELECT itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter;

Example 2: Because of the logical sequence of processing, SQL Server does not enable you to reference the alias in the GROUP BY clause. The alias is not known when the GROUP BY clause is analysed because it is executed before the SELECT clause. This also demonstrates how the alias can be used in the ORDER BY clause.

SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM(
   SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter FROM table1
) ItemNames
GROUP BY ItemName, FirstLetter

Example 3: To implement group by to alias column name in sql server below query used:

Select Dob,count(Id) as NumberofEmployees
FROM (
Select Id,Name,CreatedDate as Joindate from @temp
) subdata
group by Joindate

If you observe above query �Dob� is an alias column name in sub query and we are using same �Dob� alias column in group by outside of that statements in sql server.

Because the Having clause comes before Select, the alias declared in the SELECT portion should not be passed to the Having clause by its revealed name. As a result, the server is unaware of that alias.

The groups that do not fulfill the HAVING clause's search condition are then removed.

When SQL_COMPAT='NPS', a HAVING clause can refer to a column of a SELECT clause by either its name or its exposed name.

Example 1: one, because the select list includes the same expression that can also be used in the having clause:

SELECT COL1,COUNT(COL2) AS COL7
FROM --SOME JOIN OPERATION
GROUP BY COL1
HAVING COUNT(COL2) >= 3 

Example 2:

SELECT Major_category,
ROUND(AVG(College_jobs)/AVG(Total), 3) as Share_degree_jobs
FROM new_grads
GROUP BY Major_category
HAVING Share_degree_jobs < 0.3

Example 3: The following examples illustrate the use of exposed names of SELECT clause columns in having clauses when SQL_COMPAT='NPS':

SELECT c1 as a, COUNT(*) as c
FROM t1
GROUP BY c1 having a > 20 and c > 10;

SELECT t1.c1 as a, t1.c2+t2.c3 as b, COUNT(*) as c
FROM t1 JOIN t2 ON t1.c1 = t2.c1
GROUP BY t1.c1, b having a+5 = 10 ;

SELECT var(c1) as a
FROM t1
GROUP BY c1 having a > 200

The ORDER BY clause in SQL Server operates with the alias name of the column provided in the SELECT statement, however the WHERE, GROUP BY, and HAVING clauses do not since ORDER BY arrives after the SELECT statement in the logical query filtering process.

Example: The reason for this is that the name dob could be used as an alias name for formatted date values, and when used in the ORDER BY clause, it arranges the resultset using VARCHAR values of formatted dates rather than an actual DATE value.

SELECT customer_id,customer_name,CONVERT(VARCHAR(10),dob,101) AS dob_new
FROM CUSTOMER_MASTER
ORDER BY DOB

Now the result is ordered by DATE value and not by formatted VARCHAR value.

You would ordinarily use an alias in your FROM clause, but until an update doesn't have a FROM clause, you must mention your alias in your update clause.

Syntax:

syntax for using an alias in an update statement on SQL Server is as follows:

UPDATE Q
SET Q.TITLE = 'TEST'
FROM HOLD_TABLE Q
WHERE Q.ID = 101;

The alias should not be necessary here though.

Example :

UPDATE table1, table2 as tbl2, tbl3 as tbl3
SET table1.field1 = tbl2.field1, table1.field3=tbl3.field3
WHERE tbl3.field4 = tbl2.field4

Because MySQL allows you to alias tables from other databases, this is useful while updating a table with data from another database.

Names can get long and unwieldy as requests become increasingly complicated. We can use aliases to allocate names to objects in the query to keep things better when performing join table alias names.

Your SQL query will be easier to read and manage if you use alias.

Syntax:

SELECT t1.column_name (AS) new_column_name,
t2.column_name (AS) other_new_column_name,
...
FROM table1 (AS) t1
JOIN table2 (AS) t2
  ON t1.column_name = t2.column_name

Example 1: We can alias both table names:

SELECT ar.Title, ar.First_Author, jo.Journal_Title
FROM articles AS ar
JOIN journals  AS jo
ON ar.ISSNs = jo.ISSNs;

Example 2: Using an Alias with Non-Unique Column Names

It's common to have the same column name in two columns while working with several tables. This happens a lot with the id column. This is demonstrated in the following example:

SELECT employee.id, customer.id, employee_name, employee_department
FROM employees
INNER JOIN customer
ON employee.id = customer.employee_id

Output:

Example 3: As a result, stakeholders will be puzzled, as they will have no means of knowing which id corresponds to which database.

Creating a column alias for each id column can be beneficial:

SELECT employee.id employee_id, customer.id customer_id, employee_name, employee_department
FROM employees
INNER JOIN customer
ON employee.id = customer.employee_id

Output:

The last line is important. Your naming conventions should be simple to comprehend for all types of objects and aliases. You (or someone else) will be reading your code dozens of times for every time you type it out (at least). I will decide which one I consider to be more essential. When crafting multi-table queries, it happens.

It's often easier to prevent conflicts between two instances of the same table when using a short-hand term. SQL statements, as a declarative language, are usually short and easy to understand, with command names that sound like English. As long as they follow a consistent norm, short alias names do not cause any difficulty in this scenario.

In SQL queries, utilise aliases for table names. A good rule of thumb is to make the alias out of the first or first two letters of each capitalised table name, and you may differentiate between two aliases for the same table name by using a numerical suffix. If two tables have the same initial letter, you can use the second letter to break ties, or you can use a suitably distinguishable substring of the name.

For Example: �Site� becomes �s� and �SiteType� becomes �st�.

If the original name is longer than you want to type for the rest of the query, this option should be utilised. Although aliasing is used to improve readability even when it isn't required, most short queries don't use it.

Although there are no column name issues, you keep referring to a long table name.

It is not required to be readable. Here, conciseness is more crucial.

For instance, lsmhd would work but would be unreadable.

However, it should be used in such a way that your query is more readable in the end, not less.

When using the c# convention (# being the number attached to each), the "bad example" below does not easily differentiate between the nations and cities tables. Because it is more obvious that c and ci are different tables, the first "excellent example" is an acceptable choice (note that it is a little easier to follow what the "good example" is selecting). Furthermore, because both countries and cities have a column called "name" (e.g., "India" is a country name and "Pune" is a city name), you must indicate which table you are accessing in the first line for it to be valid SQL.

Example 1: with alias for single countries table

SELECT DISTINCT c.name
FROM countries c, cities ci1, cities ci2
WHERE ci1.country_code = c.code AND ci2.country_code = c.code
AND ci1.population >= 5000000 AND ci2.population >= 5000000 
AND ci1.id < ci2.id;

Example 2: without alias for single countries table

SELECT DISTINCT countries.name
FROM countries, cities ci1, cities ci2
WHERE ci1.country_code = countries.code AND ci2.country_code = countries.code
AND ci1.population >= 5000000 AND ci2.population >= 5000000 
AND ci1.id < ci2.id;

You can link a table to itself using a self join. It allows you to compare rows inside the same database or query hierarchical data.

The inner join or left join clause is used in a self-join. Because the self-join query refers to the same table, the table alias is used to give the same table multiple aliases inside the query.

Remember that using table aliases to reference the same table several times in a query will result in an error.

Syntax:

The following shows the syntax of joining the table T to itself:

SELECT
    select_list
FROM
    Tbl t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate;

Table is mentioned twice in the query. The table aliases t1 and t2 are used in the query to give the T table distinct names.

Example 1: Add a city name column from the city table to the customer table. As a result, we linked two tables together. A self join, for example, would mean merging the customer table with itself.

Here�s the customer table as a reminder:

The address_id column stores the customer_id of the customer�s address. For example, Customers 1 and 2 (Hari and Linda) are city_id of each other, Customers 3 and 5 (Jeni and Jegan) are city_id of each other, and so on. We can add the first name and the last name of the city_id to each record in the customer table. To do this, we need to perform a self join, that is, join the customer table to itself:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

Output:

Example 2: The staff table keeps track of each employee's ID, first name, last name, and email address. It also has a hr id field, which defines the direct hr. Maze, for instance, reports to Cate because the value of Maze's hr id is Cate.

Because Cate has no HR, the hr id column is NULL.

The self join is used to determine who reports to whom, as seen in the following query:

SELECT e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staff e
INNER JOIN sales.staff m ON m.staff_id = e.hr_id
ORDER BY
    hr_id;

In this example, we referenced to the staff table twice: one as e for the employees and the other as m for the hr. The join predicate matches employee and manager relationship using the values in the e.hr_id and m.staff_id columns.

Example 1: It will work if you enclose it in square brackets.

SELECT MAX(Value) AS [PrmTable.Value]
FROM TempTable

Example 2: A dot "." appears in alias... the column name is incorrect:

SELECT column AS 'alias.1' FROM table;

Output:

Example 3: alias column name can't contains a dot "."

SELECT 'test' AS "testing.this"
| testing.this |
test