Using a SQL Alias

Before learning how to find a database alias using Toad for Oracle, it is essential to understand what an alias is in SQL.

What is a SQL alias?

SQL database aliases are used to give a table, or a column of a table, a temporary name. And why do we want to give it a temporary name?

We use aliases in SQL queries to make column names much more readable. Often, the original names of the columns can be unreadable, which is why SQL aliases are used to simplify and better understand the reading of the SQL query.

These database aliases only last as long as the SQL query exists.

We use the AS keyword to create the column aliases. On the other hand, adding the AS keyword for table aliases is unnecessary. Instead, aliases are added after the table's name inside the FROM clause.

Alias column syntax

SELECT column_name AS alias_name
FROM table_name;

Alias table syntax

SELECT column_name(s)
FROM table_name alias_name;

References:

column_name: The original name of the column.

table_name:The original name of the table.

alias_name: The temporary name to assign.

It is important to note that if the alias_name contains spaces, we must enclose it in double-quotes.

Spaces are accepted when creating an alias for a column name. However, it is generally not good to use white space when creating an alias for a table name.

Column Aliases

We generally use column aliases to easily read the result of the SQL query by clearly identifying the headers of each column.

Example 1:

SELECT employee_id, first_name || ' ' || last_name AS "NAME"

FROM employees

WHERE last_name = 'OConnell';

Figure 1: Example of using the column alias without blanks

In this example, we have assigned the database alias NAME to the second column consisting of the concatenation of the first_name and last_name columns.

This NAME alias will be displayed as the second column header when the result set of the SQL query is returned. Since our alias_name doesn't include any spaces, we don't have to enclose the alias_name in double quotes, but if we do, it's valid, and the SQL query will execute without a problem, returning the same result.

On the other hand, if our SQL alias is made up of more than one word, we would have to enclose the alias in double quotes.

Example 2:

SELECT employee_id, first_name || ' ' || last_name AS "EMPLOYEE NAME"

FROM employees

WHERE last_name = 'OConnell';

Figure 2: Example of using column aliases with white space

We have aliased the second column concatenated with first_name and last_name, such as "EMPLOYEE NAME", in this second example. Since there are spaces in this alias_name, “EMPLOYEE NAME” must be enclosed in double quotes.

Table Aliases

When we create an alias in a table, it is generally for two reasons. First, we plan to include the same table within the FROM clause and, therefore, we need to differentiate it, or second, because we want to shorten the name of the table so that the SQL statement is shorter and easier to read.

Example 1:

SELECT e.first_name || ' ' || e.last_name "Employee Name",

       m.first_name || ' ' || m.last_name "Manager Name"

FROM Employees e, Employees m

WHERE e.employee_id = m.manager_id

In this example, we have created an alias for the employee's table called e to display the employee’s name. We have also created another alias for the same table called m to show the manager’s name. Within this SQL statement, we can refer to the employee table as e and as m.

Figure 3: Example of using database aliases for tables

Example 2:

SELECT e.first_name || ' ' || e.last_name "Employee Name",

       m.first_name || ' ' || m.last_name "Manager Name",

      d.department_name  

FROM Employees e, Employees m, Departments d

WHERE e.employee_id = m.manager_id

AND d.department_id = e.department_id

In this example, we have added the department's table with the alias d to the previous query to display the department’s name. Therefore, we can refer to the department’s table within this SQL statement as d.

Database Connections

Now that we understand how and when to use SQL aliases on database columns and tables, we can move on further.

Oracle provides two ways to identify the database we want to connect via aliases or directly using the connection string.

When we start Toad for Oracle, we have three ways to connect to the Oracle database: TNS, Direct, or LDAP.

When we select the connection option through TNSnames, a list type field is displayed in the right sector of the connection window, loaded with all the aliases defined in the TNSNames.ora file.

The TNSNames.ora file is simply a text file that can be modified with any text editor, such as Notepad. The only thing to consider is that Oracle requires that a specific syntax be met, and if it is not met, the aliases may not be displayed in the list type field.

Figure 4: SQL Database Aliases – Select List

As many users are not experts in this type of syntax, Toad for Oracle provides access to the file through a modal window accessed through the TNSNames Editor button. As we can see next to the button, a green check is shown, which means that the file exists. Otherwise, if a red cross is shown, it does not exist.

Figure 5: TNSNames Editor Button

This window has three views: the left pane shows a tree view with all available aliases, the right pane shows the syntax of the TNSNames.ora file, and the bottom pane shows the messages.

Figure 6: TNSNames Editor window

As we can see in figure 6, the TNSNames.ora file shows the six aliases that are loaded in the selection list field in the Toad for Oracle connection window.

Finally, Toad for Oracle also gives us the ability to assign an alias to our connections.

For example, in the Edit Login Record window, which is to edit the connection's properties, we can enter in the Alias field a name that is easy to read. This option is beneficial when we have many connections to different databases.

Figure 7: Edit Login Record Window

Conclusion

In this article, we have learned what SQL Aliases are and how and when to use them. In addition, we learned the different types of aliases that we have and how to find the alias of an Oracle database using the Toad for Oracle TNSNames Editor.

Related Links

Blog: Tips for Using Toad for SQL Query Generation 

Blog: How to Write a Cross-Connection Query Correctly 

Blog: Getting to Know the Three Methods of Connecting to the Database Using Toad for Oracle: TNS, Direct and LDAP 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.