Fuzzy column name

Fuzzy column names are prefixed with the same columns in two or more tables in the SQL connection. Fuzzy column names are different in different clauses. The following is an example. There are two tables, respectively, departments and employees. From these two tables, we can see that there are two identical columns (department_id, manager_id) SQL> desc departments Name Null? Type parameter -------- ---------------------------- DEPARTMENT_ID not null number (4) DEPARTMENT_NAME not null VARCHAR2 (30) MANAGER_ID NUMBER (6) LOCATION_ID NUMBER (4) SQL> desc employees Name Null? Type verification -------- ---------------------------- EMPLOYEE_ID not null number (6) FIRST_NAME VARCHAR2 (20) LAST_NAME not null VARCHAR2 (25) email not null VARCHAR2 (25) PHONE_NUMBER VARCHAR2 (20) HIRE_DATE not null date JOB_ID not null VARCHAR2 (10) salary number (8, 2) COMMISSION_PCT NUMBER (2, 2) MANAGER_ID NUMBER (6) DEPARTMENT_ID NUMBER (4) 1. in the natural join clause, the two tables have the same columns in the se The lect clause cannot contain a prefix, but does not have restrictions on non-identical columns. SQL> select department_id, e. manager_id from employees e natural join orders; select department_id, e. manager_id from employees e natural join orders * ERROR at line 1: ORA-25155: column used in NATURAL join cannot have qualifierSQL> select department_id, employees. manager_id from employees natural join orders; select department_id, employees. manager_id from employees natural join departmen Ts * ERROR at line 1: ORA-25155: column used in NATURAL join cannot have qualifier 2. in the Using clause, the same columns in the two tables must be prefixed in the select clause. However, if this column is in the using clause, the prefix cannot be added in other clauses, non-identical columns are not limited. Manager_id is the same column of the two tables and is in the using clause. Therefore, no prefix is required. Therefore, the execution is successful. SQL> select employee_id, manager_id from employees join orders using (manager_id); EMPLOYEE_ID MANAGER_ID ----------- ---------- 101 100 102 100 104 in the select list, the department_id column is the same as the two tables, the prefix is not included in the using clause. If the prefix is not added, the execution fails. SQL> select employee_id, manager_id, department_id from employees e join orders using (manager_id); select employee_id, manager_id, department_id from employees e join orders using (manager_id) * ERROR at line 1: ORA-00918: column ambiguously defined is prefixed and executed successfully. SQL> select employee_id, manager_id, e. department_id from employees e join orders ments using (manager_id); EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID ----------- ---------- ------------- 201 100 202 20 201 206 205 110 90 3. in the On clause, the two columns with the same table must be prefixed in the select clause. Non-identical columns are unrestricted. If the manager_id of the same column is not prefixed, execution fails. SQL> select employee_id, manager_id from employees e join orders d on (e. manager_id = d. manager_id); select maid, manager_id from employees e join orders d on (e. manager_id = d. manager_id) * ERROR at line 1: ORA-00918: column ambiguously defined prefix, execution successful. SQL> select employee_id, e. manager_id from employees e join orders ments d on (e. manager_id = d. manager_id); EMPLOYEE_ID MANAGER_ID ----------- ---------- 101 100 102 100 104 103 105 103 4. in oracle syntax, the same columns of the two tables must be prefixed in the select clause. Non-identical columns are unrestricted. If the manager_id of the same column is not prefixed, execution fails. SQL> select employee_id, manager_id, department_name from employees, departments 2 where employees. manager_id = Administrative ments. manager_id; select employee_id, manager_id, department_name from employees, parameters * ERROR at line 1: ORA-00918: column ambiguously defined plus prefix, execution successful. SQL> select employee_id, employees. manager_id, department_name from employees, deployments 2 where employees. manager_id = Administrative ments. manager_id; EMPLOYEE_ID MANAGER_ID DEPARTMENT_NAME ----------- ---------- ------------------------------ 101 100 102 100 IT 104 103