When should you write a table name in a query?

Question

SQL

Here is a simple example of joining the persons and positions tables
Working query

SELECT id_person, name, id_pos, title 
FROM persons
INNER JOIN positions ON id_pos = position_ref

But we can also write such a request

SELECT id_person, name, id_pos, title 
FROM persons
INNER JOIN positions ON positions.id_pos = position_ref

When do you need to write a table name in a query and what is the reason for this?

PS
Some join queries (LEFT OUTER JOIN ??) did not work for me without a table name.

Answer 1

If there are columns with the same names in different tables.
Sometimes SQL tolerates this (for example, you can write SELECT * FROM table1 CROSS JOIN table2even if the tables have such columns), but more often not. In the JOIN ON predicate or in the WHERE predicate, you need to know which table column is in question.

Answer 2

Each is the creator of his hemorrhoids.
Strong recommendation. If only one table is the source of the query records (total, whole), the field names can be omitted. In all other cases, specify a table alias for absolutely every field (including generic field names in USING and NATURAL JOIN), except for the output set field names used in post-grouping expressions. This will ensure that there are no ambiguities.
The term "table" should be understood as strictly one instance of the original data set. In other words, a query that uses two copies of the same table, or uses an additional synthetic table of constants, is not a query with a data source from one table.
---
In some dialects, an alias may also be mandatory due to the interference of names from different spaces.
For example, in MySQL stored objects, when a field name of a table matches the name of a local variable, access without specifying an alias is always access to the variable. Therefore, for MySQL, the rule is even stricter - always specify an alias.
And even such rigor is sometimes insufficient. Example . Pay attention to sorting - in it id is interpreted as a variable name, and not a field name of the output set, and therefore the result is "strange". By the way, this is the case when nothing at all will help, except for the expression of the output field instead of its name.

Answer 3

galaxy , here's another example
(I remember where it didn't work)
Each table has a column named id. The rest of the column names are different.
It works

SELECT ProductCode, Name FROM table_one 
LEFT OUTER JOIN table_two 
ON  table_two.id = table_one.id_client;

It's not

SELECT ProductCode, Name FROM table_one 
LEFT OUTER JOIN table_two 
ON  id = table_one.id_client;

[Err] 1052 - Column 'id' in on clause is ambiguous
Is it enough just to match the names of any columns in the table structure ()?
That is, look - id and id_client are compared in ON. The names are different.
It is clear what is associated with what.
And even if ON id = id, it is clear that you need to compare the id from the first table with the id from the second.
We have 2 tables. And each id. Nothing else to compare. There cannot be two identical fields in a table.

Answer 4

Always call each table in the query a short name, and use this short name in naming columns, then under no circumstances will there be problems with name conflicts.

SELECT t1.ProductCode, t2.Name
FROM table_one as t1 -- t1 - краткий псевдоним таблицы table_one в пределах select-а
LEFT OUTER JOIN table_two as t2 -- t2 - краткий псевдоним таблицы table_two в пределах select-а
ON  t2.id = t1.id_client;