SQL Statements in MySQL, the use of the dot operator

Question

The following is the SQL statement that I found:

SELECT d.name, count(e.emp_id) num_employees
FROM department d INNER JOIN employee e
  ON d.dept_id = e.dept_id
GROUP BY d.name
HAVING count(e.emp_id) > 2;

I wanted to know what d.name, e.name or d.dept_id mean. What is the difference?

Solution 1

The dot . is usually the table name. In the sentence you mention, d is the name of the alias name. If you check the FROM statement, you have department d. That means that you want to fetch rows from the table department but reference it as d (because department is a long word), so you create an alias.

In order to disambiguate fields, as different tables may have the same field names, you can prepend the field with the table name (or alias in you case) followed by the dot.

So in short you're saying:

Select the field name from alias d, and count the number of employees from the join of tables department and employee, aliased d and e respectively, etc.

Solution 2

From Dev.MySQL.com:

You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly.
You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference unless the reference would be ambiguous.

---

Identifier Qualifiers

MySQL permits names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (“.”) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

---

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
WHERE t1.name = t2.name;

Comments

[edgards]

thank you, you help me a lot, saving me from the confusion

[nKn]

You're welcome. Feel free to accept this answer so other users may know it is the one that helped you best. You can also wait a time so other users can also answer and then accept the answer that helped you most (this is optional but recommended).