0

I asked a question about aliases recently: Discerning between alias, temp table, etc [SQL Server].

I got the impression that tables and resulting queries had to be named using aliases.

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders 
)

In fact when you use an alias there is a runtime error. What gives?

Dale K
  • 25,246
  • 15
  • 42
  • 71
itstoocold
  • 2,385
  • 2
  • 12
  • 15
  • Your `IN` clause doesn't have to be aliased, only a derived table would need an alias. I think you are confusing sub-queries and derived tables. Check [this blog](https://bertwagner.com/2019/04/23/correlated-subqueries-vs-derived-tables/) for an explanation. – S3S Jul 30 '19 at 19:59

2 Answers2

1

When working with "tables" - that is, anything that can use a JOIN - a name of some sort is needed. For example, if your query was written as:

select customers.name as 'Customers'
from customers
LEFT JOIN (
    select customerid from orders
    ) ___
WHERE ___ is null

Then you need to name the derived table, and fill in the blanks, because SQL Syntax requires a name in a JOIN statement.

However, in your sample code:

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders 
)

The syntax does not require a name, and so the nested query does not require naming.

S3S
  • 24,809
  • 5
  • 26
  • 45
Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28
-1

Aliases are there for convenience most of the time. There are times when you are required to use them, though.

https://www.geeksforgeeks.org/sql-aliases/

Temporary tables, derived look-ups (sub-queries), common table expressions (CTEs), duplicate table names in JOINs, and a couple other other things I'm sure I'm forgetting are the only times you need to use an alias.

Most of the time, it's simply to rename something because it's long, complex, a duplicate column name, or just to make things simpler or more readable.

The query you post won't likely need an alias, but using one makes things easier when you are using the results in code, as well as when/if you add more columns to the query.

Side note:
You may see a lot of single letter abbreviations in people's SQL. This is common, however, it's bad form. They will also likely abbreviate with the first letter of every word in a table name, such as cal for ClientAddressLookup, and this is also not great form, however typing ClientAddressLookup for each of the 12 columns you need when JOINing with other tables isn't great either. I'm as guilty of this as everyone else, just know that using good aliases are just as necessary and useful as using good names for your variables in code.

computercarguy
  • 2,173
  • 1
  • 13
  • 27
  • I don't suppose there's anyway I can get feedback on the down vote. I don't see much difference between my answer and the various answers https://stackoverflow.com/questions/10433183/when-is-it-required-to-give-a-table-name-an-alias-in-sql, so I'm interested on why someone might have a problem with my answer. – computercarguy Aug 02 '19 at 15:59