1. What is the USING Clause in Oracle JOIN?
The USING clause is used in a JOIN when both tables have a column with the same name and compatible data type.
It eliminates the need to write the join condition with the ON clause.
Syntax
SELECT column_list
FROM table1
JOIN table2
USING (common_column);2. Why do we use the USING clause?
A USING clause is used to:
- Simplify join syntax.
- Avoid repeating identical column names.
- Improve query readability.
- Join tables using common columns.
3. What is the syntax of the USING clause?
SELECT columns
FROM table1
JOIN table2
USING (column_name);Example:
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id);4. How does the USING clause work?
Suppose we have:
EMPLOYEES
EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID 101 John 10 102 David 20 103 Scott 30
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME 10 Sales 20 HR 30 Finance
Query
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id);Output
Employee Department John Sales David HR Scott Finance
5. What is the difference between USING and ON?
| USING | ON |
|---|---|
| Used when column names are identical | Can use different column names |
| Simpler syntax | More flexible |
| Column name written only once | Both table columns must be specified |
| Cannot use expressions in the join condition | Can use expressions and multiple conditions |
Example using ON:
SELECT employee_name,
department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;Example using USING:
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id);6. Can USING be used with multiple columns?
Yes.
SELECT *
FROM employees
JOIN departments
USING
(
department_id,
location_id
);Both columns must exist in both tables with the same names and compatible data types.
7. Can USING be used with different column names?
No.
Example:
Table A: dept_id
Table B: department_id
You must use ON.
SELECT *
FROM employees e
JOIN departments d
ON e.dept_id = d.department_id;8. Can USING be used with INNER JOIN?
Yes.
SELECT employee_name,
department_name
FROM employees
INNER JOIN departments
USING (department_id);9. Can USING be used with LEFT JOIN?
Yes.
SELECT employee_name,
department_name
FROM employees
LEFT JOIN departments
USING (department_id);Returns all employees, including those without matching departments.
10. Can USING be used with RIGHT JOIN?
Yes.
SELECT employee_name,
department_name
FROM employees
RIGHT JOIN departments
USING (department_id);Returns all departments, even if no employee belongs to them.
11. Can USING be used with FULL OUTER JOIN?
Yes.
SELECT employee_name,
department_name
FROM employees
FULL OUTER JOIN departments
USING (department_id);12. Can USING be used with CROSS JOIN?
No.
A CROSS JOIN does not have a join condition.
Incorrect:
SELECT *
FROM employees
CROSS JOIN departments
USING (department_id);13. Can table aliases be used with USING?
Yes.
SELECT employee_name,
department_name
FROM employees e
JOIN departments d
USING (department_id);After a USING clause, the join column is treated as a single column in the result. Do not qualify the join column with a table alias in the SELECT list.
Correct:
SELECT department_id,
employee_name
FROM employees e
JOIN departments d
USING (department_id);Incorrect:
SELECT e.department_id
FROM employees e
JOIN departments d
USING (department_id);This raises:
ORA-25154: column part of USING clause cannot have qualifier
14. What columns appear in the result?
The join column appears only once.
Example:
Tables:
EMPLOYEES
DEPARTMENT_ID
DEPARTMENTS
DEPARTMENT_ID
Output:
DEPARTMENT_ID only once.
15. Can USING be used with WHERE?
Yes.
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id)
WHERE department_name = 'Sales';16. Can USING be combined with GROUP BY?
Yes.
SELECT department_name,
COUNT(*)
FROM employees
JOIN departments
USING (department_id)
GROUP BY department_name;17. Can USING be combined with ORDER BY?
Yes.
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id)
ORDER BY department_name;18. What are the advantages of USING?
- Simple syntax.
- Less typing.
- Improved readability.
- Avoids repeating identical column names.
- Returns the join column only once.
19. What are the disadvantages of USING?
- Only works when column names are identical.
- Cannot join on expressions.
- Cannot compare differently named columns.
- Less flexible than
ON.
20. Common Errors with USING
| Error | Cause |
|---|---|
| ORA-25154 | Qualified a column that is part of the USING clause (for example, e.department_id) |
| ORA-00904 | Invalid column name |
| ORA-00942 | Table or view does not exist |
| ORA-00933 | SQL command not properly ended |
21. Real-Time Example
Suppose an HR database has:
EMPLOYEES
EMPLOYEE_NAME DEPARTMENT_ID John 10 David 20
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME 10 Sales 20 HR
Query
SELECT employee_name,
department_name
FROM employees
JOIN departments
USING (department_id);Output
Employee Department John Sales David HR
22. USING vs ON vs NATURAL JOIN
| Feature | USING | ON | NATURAL JOIN |
|---|---|---|---|
| Same column names required | Yes | No | Yes |
| Specify join condition | Partially (column list only) | Yes | No |
| Multiple columns supported | Yes | Yes | Yes (automatic) |
| Join column appears once | Yes | No (unless explicitly selected) | Yes |
| Recommended for production | Yes | Yes | Rarely |
23. When should you use USING instead of ON?
Use USING when:
- The join columns have the same name in both tables.
- The join condition is a simple equality comparison.
- You want shorter, cleaner SQL.
Use ON when:
- Column names differ.
- Multiple or complex join conditions are required.
- You need expressions or non-equality conditions in the join.
USING clause is helpful for cleaner joins when column names match, but ON remains the more flexible and widely used option.
No comments:
Post a Comment