Sql Using On And Using Clauses Complete Guide

 Last Update:2025-06-23T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    9 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of SQL Using ON and USING Clauses

Introduction

Joining tables is a common operation in SQL used to combine data from two or more tables into a single result set based on a related column or columns. The ON and USING clauses are pivotal for defining the join criteria.

ON Clause

Usage

The ON clause is utilized to define the condition that must be met for rows from two tables to be combined in the result set. It allows for joining tables based on multiple conditions.

SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column2 AND table1.column3 = table2.column4;

Important Info

  • Flexibility: The ON clause is highly flexible as it can be used to specify complex join conditions.
  • Multiple Conditions: You can include multiple conditions using logical operators like AND, OR.
  • Different Columns: ON allows specifying any column(s) from either (or both) tables to match upon, not just common keys.
  • Performance Considerations: When using ON, ensure that the columns specified align with the indexes on the tables for better performance.

USING Clause

Usage

The USING clause is used when there is at least one column with the exact same name in the two tables you wish to join. It simplifies the join syntax by automatically matching these columns.

SELECT *
FROM table1
JOIN table2
USING (common_column);

Note: If you want to specify more than one column using the USING clause, they still must have the same names across the tables:

SELECT *
FROM table1
JOIN table2
USING (column1, column2);

Important Info

  • Simplified Syntax: The USING clause can simplify your SQL code by automatically performing the join on columns with the same name.
  • Single Column Names Only: It requires that the columns being joined have identical names.
  • Result Set: When using USING, the duplicate column values are included only once in the result set.
  • Readability: For joins where the columns share the same name, USING increases readability as the condition is implicit.
  • Alias Requirement: When needing to refer to a column in the USING clause within the SELECT statement, you must use an alias since the column is not duplicated.

Example with Alias:

SELECT t1.common_column AS col1, t2.common_column AS col2, other_col1, other_col2
FROM table1 t1
JOIN table2 t2
USING (common_column);

Differences between ON and USING

  • Column Matching: ON requires explicit specification of the columns to join, while USING implicitly joins based on columns sharing the same name.
  • Complexity: ON supports more complex conditions and different column names, making it versatile for various scenarios.
  • Result Set: ON includes duplicate columns for matched data in the result set, whereas USING excludes duplicates.
  • Ambiguity: With USING, there's no need to resolve column name conflicts as the columns are listed only once in the output.

Practical Scenarios

Using ON:

When joining on distinct column names or when additional filtering conditions are required:

-- Joining tables on distinct keys
SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
JOIN customers 
ON orders.cust_id = customers.customer_id;

-- Adding an extra condition
SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
JOIN customers 
ON orders.cust_id = customers.customer_id AND orders.status = 'Pending';

Using USING:

When tables to be joined have common column names:

SELECT order_id, customer_name, order_date
FROM orders
JOIN customers 
USING (customer_id);

-- Joining multiple common columns
SELECT order_id, customer_name, employee_name, product_name
FROM orders
JOIN order_details 
USING (order_id)
JOIN employees 
USING (employee_id);

Conclusion

In summary, the choice between ON and USING in SQL primarily depends on the complexity of the join conditions and whether the tables to be joined have identical column names. Understanding these nuances helps in writing efficient and readable SQL queries.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Using ON and USING Clauses

Introduction

In SQL, ON and USING clauses are used in JOIN operations to specify how to combine rows from two or more tables.

  • ON Clause: Provides more flexibility by allowing you to specify any condition for joining tables.
  • USING Clause: Simplifies the syntax when you join tables on columns having the same name.

Table Setup

Before we dive into the examples, let's create two tables that we will be using throughout the examples. These tables will be employees and departments.

Create the departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Insert sample data into the departments table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'IT'),
(4, 'Human Resources');

Create the employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert sample data into the employees table
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(101, 'John Doe', 1, 60000.00),
(102, 'Jane Smith', 2, 75000.00),
(103, 'Alice Johnson', 3, 85000.00),
(104, 'Bob Brown', 4, 65000.00),
(105, 'Charlie Davis', 5, 70000.00); -- Notice: department_id 5 does not exist in the departments table

Using the ON Clause

The ON clause is used to specify the condition that must be met for rows to be joined from two tables. This can be based on matching column values, comparisons, or even more complex conditions.

Example 1: Join employees and departments Using ON Clause

SELECT 
    employees.employee_id,
    employees.employee_name,
    departments.department_name,
    employees.salary
FROM 
    employees
INNER JOIN 
    departments ON employees.department_id = departments.department_id;

Explanation:

  • The JOIN operation combines rows from both the employees and departments tables.
  • The ON clause specifies that a row from the employees table is joined with a row from the departments table if their department_id values match.
  • The result includes employee details along with their corresponding department names.

Result:

| employee_id | employee_name | department_name | salary | |-------------|---------------|-----------------|---------| | 101 | John Doe | Sales | 60000.00| | 102 | Jane Smith | Marketing | 75000.00| | 103 | Alice Johnson | IT | 85000.00| | 104 | Bob Brown | Human Resources | 65000.00|

Example 2: Join with a Condition Using ON Clause

SELECT 
    employees.employee_id,
    employees.employee_name,
    departments.department_name,
    employees.salary
FROM 
    employees
INNER JOIN 
    departments ON employees.department_id = departments.department_id
WHERE employees.salary > 70000;

Explanation:

  • This query is similar to the previous one, but it adds a WHERE clause to filter employees with a salary greater than 70,000.
  • Only employees from the employees table who meet this condition and have a matching department_id in the departments table are included in the result.

Result:

| employee_id | employee_name | department_name | salary | |-------------|---------------|-----------------|---------| | 102 | Jane Smith | Marketing | 75000.00| | 103 | Alice Johnson | IT | 85000.00|

Using the USING Clause

The USING clause simplifies the JOIN operation when the columns being used for the join have the same name in both tables. It automatically joins the tables on those matching columns.

Example 3: Join employees and departments Using USING Clause

SELECT 
    employee_id,
    employee_name,
    department_name,
    salary
FROM 
    employees
INNER JOIN 
    departments USING (department_id);

Explanation:

  • This query joins the employees and departments tables using the USING clause.
  • Since both tables have a department_id column, USING (department_id) automatically joins the tables on this column.
  • The columns department_id will appear only once in the result set.

Result:

| employee_id | employee_name | department_name | salary | |-------------|---------------|-----------------|---------| | 101 | John Doe | Sales | 60000.00| | 102 | Jane Smith | Marketing | 75000.00| | 103 | Alice Johnson | IT | 85000.00| | 104 | Bob Brown | Human Resources | 65000.00|

Example 4: Join with USING Clause and Filter Data

SELECT 
    employee_id,
    employee_name,
    department_name,
    salary
FROM 
    employees
INNER JOIN 
    departments USING (department_id)
WHERE salary < 80000;

Explanation:

  • This query is similar to the previous example but includes a WHERE clause to filter employees with a salary less than 80,000.
  • Only employees from the employees table who meet this condition and have a matching department_id in the departments table are included in the result.

Result:

| employee_id | employee_name | department_name | salary | |-------------|---------------|-----------------|---------| | 101 | John Doe | Sales | 60000.00| | 102 | Jane Smith | Marketing | 75000.00| | 104 | Bob Brown | Human Resources | 65000.00|

Key Differences Between ON and USING Clauses

  • ON Clause:

    • More flexible; can join based on any condition.
    • Can join on columns with different names or even on multiple columns.
    • May result in duplicate columns if the ON condition includes columns from both tables with the same name.

  • USING Clause:

    • Simpler syntax when joining tables on columns with the same name.
    • Automatically handles duplicate columns by showing them only once.
    • Limited to using a single column or a list of columns with the same name in both tables.

Comparison Example

-- Using ON Clause
SELECT 
    employees.employee_id,
    employees.employee_name,
    departments.department_name,
    employees.department_id  -- department_id appears twice
FROM 
    employees
INNER JOIN 
    departments ON employees.department_id = departments.department_id;

-- Using USING Clause
SELECT 
    employee_id,
    employee_name,
    department_name,
    department_id  -- department_id appears only once
FROM 
    employees
INNER JOIN 
    departments USING (department_id);

Summary

  • ON Clause: Use for complex join conditions or when columns have different names.
  • USING Clause: Use for simple join conditions where columns have the same name in both tables to avoid duplication.

By understanding and applying these clauses effectively, you can perform more complex and efficient SQL queries to retrieve and manipulate data from multiple tables. Practice with your own tables and datasets to deepen your understanding.

Top 10 Interview Questions & Answers on SQL Using ON and USING Clauses

Top 10 Questions and Answers on SQL Using ON and USING Clauses

    • ON Clause: This clause is used in join operations to specify the condition for joining two tables. It is more flexible and allows the use of complex join conditions involving multiple columns or expressions.
    • USING Clause: This clause is used for joining tables when the join condition involves columns with the same name in both tables. It simplifies the join condition by automatically assuming you want to join on columns with matching names, and it eliminates duplicate columns from the result set.

  2. Can you explain the difference between JOIN using ON and USING in the context of a natural join?

    • A Natural Join automatically joins tables on all columns with the same name. However, it can lead to ambiguous results if there are many matching columns. An INNER JOIN with USING can be considered a controlled version of a natural join because it requires you to specify only the common column(s) to join on, thus avoiding ambiguity and improving readability.
    • An INNER JOIN with ON, on the other hand, gives you more control and flexibility to specify complex join conditions, but it requires you to explicitly list all columns involved in the join.

  3. What is the advantage of using the USING clause over the ON clause?

    • The main advantage of the USING clause is its simplicity. It eliminates the need to specify the same column twice in the SELECT statement and can help avoid column ambiguity in the result set. It makes the query shorter and easier to read, especially when joining tables on a single column with the same name.

  4. When should you use the ON clause instead of the USING clause?

    • Use the ON clause when you need to join tables on multiple columns, or when the columns do not share the same name. Additionally, use ON when you need to apply more complex join conditions, such as joining on expressions or conditions involving more than one column.

  5. How do you use the USING clause with multiple columns?

    • Although the USING clause is typically used with a single column, many SQL databases support using multiple columns with it. This involves specifying a list of columns within parentheses: 
      SELECT *
FROM table1
JOIN table2 USING(column1, column2);
    • This statement joins table1 and table2 on the pairs of columns column1 and column2 from both tables.

  6. Can you use the USING clause with different column names?

    • No, the USING clause requires the columns to have the same name in both tables. If the columns have different names, you must use the ON clause to specify the join condition explicitly.

  7. How does the handling of columns differ in JOINs with ON versus JOINs with USING?

    • In a JOIN with ON, all columns from both tables are included in the result set unless specified otherwise. This can lead to duplicate columns if the same column appears in both tables.
    • In a JOIN with USING, only one instance of each common column is included in the result set, effectively eliminating the duplicates.

  8. Does the USING clause work with OUTER JOINs?

    • Yes, the USING clause can be used with all types of joins, including INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Here’s an example with a LEFT OUTER JOIN: 
      SELECT *
FROM table1
LEFT OUTER JOIN table2 USING(column_name);
    • This query will return all rows from table1 and the matched rows from table2. If no match is found, NULLs will be used to fill in the columns from table2.

  9. Can you use the USING clause in a self join?

    • No, you cannot use the USING clause in a self join because it requires two different tables with columns of the same name. In a self join, the same table is joined to itself, so you must use the ON clause to specify the join condition explicitly: 
      SELECT a.column_name, b.column_name
FROM table_a a
JOIN table_a b ON a.matching_column = b.matching_column;

  10. How do you combine the ON and USING clauses in a single query?

    • SQL does not allow combining the ON and USING clauses in the same join because they serve different purposes and would lead to ambiguous join conditions. However, you can chain multiple joins together, using each clause where appropriate:

You May Like This Related .NET Topic

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.