![]() This results in massive time savings if you have to update a large number of existing queries, functions, and stored procedures.Ī self-join allows you to join a table to itself. Now when we go to update our table, we only need to make the change in one place: Our first query, when updated to use an alias, might look like this: Let’s see how aliases help with this issue. Imagine you had to update multiple stored procedures and functions because of this change. This may not seem like a big step, but this is a short, simple query. SELECT customers_europe.id, customers_europe.name, customers_europe.age, client.id You would have to make the change everywhere this table is referenced: Now imagine there was a change to our customer table, like being renamed to customers_europe. SELECT customer.id, customer.name, customer.age, client.id Imagine you had a query like the one below: Improve Query MaintainabilityĪn alias also helps with maintainability. The same goes for any badly named tables you encounter in your database. This problem can be reduced by using an alias in any query that includes this column. It’s not immediately obvious what this column represents you would need the data model’s documentation or someone explaining it to you to decipher its meaning. For example, imagine there was a column called cn_index that stored customer names. ![]() Now we get a much more understandable result: employee_idĪn alias can simplify and improve the readability of your query. SELECT employee.id employee_id, customer.id customer_id, employee_name, employee_department Introducing a column alias for each id column can help: This result will confuse stakeholders, as they have no way of telling which id belongs to which table. SELECT employee.id, customer.id, employee_name, employee_department Here’s an example that demonstrates this: This frequently occurs with the id column. When you’re dealing with multiple tables, it’s common to have the same column name in both tables. Using an Alias with Non-Unique Column Names The more exposure to practical examples you get, the better off you will be. SQL JOINs are a tricky topic – they’re included in this article about SQL Topics That Require Practice. In this section, we’ll look at some common alias use cases and explain how to implement each one. Let’s see some practical examples of using aliases with JOIN! Examples of Using SQL Alias with JOIN ![]() That concludes our look at SQL alias syntax. You can see how aliases help us access the correct table at each part of the query. The subquery is the part of the query in bold type. Let’s look at the syntax of how to use aliases in a subquery. You can see we follow this rule by including t1 and t2 in our SELECT clause:Īn SQL subquery is a query inside another query. When you rename database tables, you must use the table aliases in the ON condition, in the SELECT list when selecting the columns (if there's a need to specify the table for the column), and in other parts of the query. We declare the alias for table1 to be t1. SQL Table AliasesĪs before, the inclusion of the AS keyword is completely optional. ![]() Ok, that covers SQL aliases for column names. SELECT t1.column_name (AS) 'new column name'Įxcluding the single quotation marks here would cause an error. If you want to include spaces in your new column name, enclose it in quotes. In this case, our aliases are new_column_name and other_new_column_name. The alias is defined directly after the column name (or the AS keyword). You can include it if you want, but the query will behave exactly the same regardless. T2.column_name (AS) other_new_column_name The exercises are based on real-world problems you'll solve daily in your work. It will provide you with a solid practical foundation of SQL. It contains over 120 hands-on SQL exercises. If you feel overwhelmed by this syntax, consider getting to grips with the basics of SQL queries by enrolling in our interactive SQL Basics course. T2.column_name (AS) other_new_column_name, The syntax shown below covers both column and table aliases: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |