This blog will demonstrate how the ON clause differs from the USING clause in MySQL.
What is the Difference Between ON Clause and USING Clause in MySQL?
In MySQL, both the “ON” and the “USING” clauses are utilized to join tables in a “SELECT” statement, as they are not interchangeable and serve different purposes. To comprehend these differences, you need to understand both the ON and the USING clauses via practical examples.
What is the ON Clause in MySQL?
In MySQL, the “ON” clause is a SQL keyword that is utilized to join two or more tables based on a specific condition that evaluates to true or false. The below snippet shows the syntax of MySQL’s ON clause:
FROM [table_name_1]
JOIN [table_name_2]
ON [table_name_1].[column_name] = [table_name_2].[column_name];
In the above syntax, “[table_name_1]” and “[table_name_2]” are the tables that will be joined with the “JOIN” clause, and the condition is applied using the “ON” clause.
Here is an example of using the “ON” clause with the “SELECT” statement and “JOIN” clause:
FROM lh_orders
JOIN lh_customers
ON lh_orders.customer_id = lh_customers.customer_id;
In the above example, the “lh_orders” and “lh_customers” are the tables, and the condition is specified to the “customer_id” of both tables.
Output
The output showed that both tables have been joined under the particular condition, and there are two “customer_id” columns in the output, one is from “lh_orders” and the other one is from “lh_customers”.
What is the USING Clause in MySQL?
In MySQL, the “USING” clause is a SQL keyword that is utilized to join two or more tables based on a specific condition that involves columns with the same name in both tables. The syntax of the “USING” clause is depicted in the following code block:
FROM [table_name_1]
JOIN [table_name_2]
USING ([column_name]);
In the above syntax, the “[column_name]” is the column that has the same name in both tables.
An example of utilizing the “USING” clause with the “SELECT” statement and the “JOIN” clause is given below:
FROM lh_orders
JOIN lh_customers
USING (customer_id);
In the above example, the “USING” clause is utilized with the “customer_id” column of both tables.
Output
The output showed that both tables have been joined and there is only one “customer_id” column in the output.
Now let’s learn how MySQL’s ON clause differs from the USING clause.
ON vs USING in MySQL
The “ON” clause and the “USING” clause are both used to join tables in MySQL, but they differ in their syntax and usage. Some common differences among the stated clauses are depicted in the following table:
| ON Clause | USING Clause | |
|---|---|---|
| Syntax | JOIN [table_name] ON [join_condition] | JOIN [table_name] USING ([column_name]) |
| Purpose | Used to specify any join condition that evaluates to true or false | Used to specify join condition based on columns with the same name |
| Join Conditions | Can use any join conditions, including non-equality conditions such as greater than, not equal to, less than, etc. | For equality comparisons, both tables must have the same column names |
| Flexibility | More flexible, can handle more complex join conditions | Less flexible, can only be used for simple join conditions based on identical column names |
| Clarity | Can be more verbose, but provides greater clarity and precision | Can be more concise but may be less precise in describing the join condition |
| NULL Values | It can handle NULL entries with the help of IS NULL operator | The USING clause does not handle NULL values well, as it only compares columns using the equality operator. |
| Resulting Columns | The ON clause retains all columns from both tables in the result set, including duplicates. | The USING clause retains only one column for each matching pair of columns and excludes duplicates. |
That’s all from this MySQL guide.
Conclusion
The ON and USING clauses are utilized to join tables in MySQL, but they have different syntax and usage. The ON clause is more flexible and can handle more complex join conditions as well as NULL values, while the USING clause is less flexible and can only be used for simple join conditions based on identical column names. This guide has explained detailed information on the difference between the ON and USING clauses of MySQL.
