Backticks preventing simple JOIN query-CodePudding

I'm just starting out..experimenting with simple queries. In the JOIN query below, the backticks in the FROM clause prevent the query from running - error is "Unrecognized name: employees at [9:5]" Remove the backticks, however, and the query runs just fine. Why is this? Thank you!

SELECT
   employees.name AS employee_name,
   employees.role AS employee_role,
   departments.name AS department_name
FROM 
    `employees_data.employees`
INNER JOIN 
    employees_data.departments ON
    employees.department_id = departments.department_id

CodePudding user response：

If you are using quoted identifiers, you must quote the database and table name separately, thusly:

SELECT
   employees.name AS employee_name,
   employees.role AS employee_role,
   departments.name AS department_name
FROM 
    `employees_data`.`employees`
INNER JOIN 
    employees_data.departments ON
    employees.department_id = departments.department_id

CodePudding user response：

When you quote an identifier like employees_data.employees that means the whole identifier. from `employees_data.employees` quote the .. It means "from the table employees_data.employees". In contrast to from employees_data.employees or from `employees_data`.`employees` which means "from the table employees in the schema employees_data".

Quote the individual identifiers, employees_data and employees; the . is syntax.

In general, don't quote identifiers if you don't have to. It can introduce other odd behavior. For example, SQL is generally case-insensitive, but quoting identifiers will make it case-sensitive. employees will match employees or Employees or EMPLOYEES but `employees` only matches employees.

Also, use schema qualifiers consistently. In your query sometimes you do and sometimes you don't. For example, your inner join is confusing. You're joining employees_data.departments on employees and departments. Is departments the same as employees_data.departments? Is employees_data.employees the same as employees?

If they're all in the same schema, don't use schemas. Fully qualifying your table names unnecessarily artificially restricts your query to only work on that schema; your query will break if the schema name changes.

SELECT
   employees.name AS employee_name,
   employees.role AS employee_role,
   departments.name AS department_name
FROM 
    employees
INNER JOIN 
    departments ON employees.department_id = departments.department_id

This will query tables in the current schema, regardless of its name.