is there a name for adding an a. in a.table in an sql query?

Question

Person is a table with columns PersonId, FirstName, LastName

Address is a table with columns PersonId, City, State

SELECT a.FirstName, a.LastName, b.City, b.State
FROM Person a, Address b
WHERE a.PersonId = b.PersonId;

I have two questions.

1. Please correct me if I'm wrong, but I'm guessing that the purpose of the (a., b.) extensions are used to indicate a specific column of an SQL table so there exists no ambiguity between selecting a column given two tables may have the same column name?

2. Is there a name for this?

Answer 1

Generally it's called an identifier qualifier:

https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

...a column name may be given a table-name qualifier, which itself may be given a database-name qualifier. Examples of unqualified and qualified column references in SELECT statements:

SELECT c1 FROM mytable
WHERE c2 > 100;

SELECT mytable.c1 FROM mytable
WHERE mytable.c2 > 100;

SELECT mydb.mytable.c1 FROM mydb.mytable
WHERE mydb.mytable.c2 > 100;

You can use a table alias as a qualifier either to make it shorter than the full table name, or because you are doing a self-join and you use aliases to make more than one reference to the same table name.

SELECT c1, c2, t1.c FROM db1.t AS t1 INNER JOIN db2.t AS t2
WHERE t2.c > 100;

Answer 2

These are called table aliases, and indeed their purpose is to indicate from which table each column comes from. This is mandatory to avoid ambiguity when a column by the same name exists in both tables - but also a general good practice, so people reading the query can understand it without having knowledge of the underlying table structures.

Note that you don’t necessarily need explicit aliases; you can also prefix the columns with the full table name if you like (like Person.FirstName): aliases just makes things shorter to write.

You should use meaningful aliases so it is easier to remember them through the query (Person would be best aliases p than a).

Finally, you should be using explicit, modern joins (with the on keyword) rather than old-school, implicit joins, whose syntax is not state of the art since ANSI SQL 92, decades ago.

Your query:

SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person p
INNER JOIN Address a ON a.PersonId = p.PersonId;

Answer 3

2. Is there a name for this?

It's called giving identifier. In SELECT city.Name FROM city; city is the identifier to Name, in SELECT c.Name FROM city c; however alias c is the identifier to Name.

1. Please correct me if I'm wrong, but I'm guessing that the purpose of the (a., b.) extensions are used to indicate a specific column of an SQL table so there exists no ambiguity between selecting a column given two tables may have the same column name?

This is true apart from the aliases. One can achieve this without aliases by referencing the exact names of the tables.

Aliases help,

• Changing the related table name in one place and in one place alone

  e.g.

  changing sales to stock in

    SELECT *
    FROM sales s
    WHERE s.col1 ... s.col2 ... s.col3 ...
    -- WHERE sales.col1 ... sales.col2 ... sales.col3 ...

is relatively easier.

• Simplify the details of the query by shortening the columns refered

  e.g.

  It's harder to understand the query below than the one where it has simple aliases:

  SELECT 
    product_color_configuration.Name
  FROM product_color_configuration
  LEFT JOIN product_channel_permission ON product_color_configuration.ProductId = product_channel_permission.ProductId ...
  

• Remove the ambiguity when the same table is referenced multiple times distinctly:

  SELECT
    COALESCE(c1.Name, c2.Name) -- if Id exists prioritize
  FROM table t
  LEFT JOIN city c1 ON t.CityId = c1.Id
  LEFT JOIN city c2 ON c2.Name REGEXP t.CityGuessedName
  ;