USING is a cleaner join syntax that lets you join tables by a column name, as long as that column exists in both tables with the same name.
Using an example from Leetcode it asks us to:
Write a solution to report the name and bonus amount of each employee who satisfies either of the following:
- The employee has a bonus less than 1000.
- The employee did not get any bonus.
- Return the result table in any order.
The result format is in the following example:
When you’re joining tables in SQL, you’ll usually write something like:
...ON employee.empId = bonus.empId
That works everywhere, and it’s explicit. But it can get repetitive, especially when your join key has the same name in both tables.
That’s where USING comes in.
When you can use USING
- Start with every row in Employee.
- Try to match a row in Bonus using empId.
- If no match exists, the bonus fields come out as NULL.
- Then filter:
- keep employees where the bonus is under 1000
- or where the bonus is NULL (meaning they didn’t get any bonus)
Both tables contain the join column
The join column has the same name in both tables
You are doing an equality join on that column
When you can't use USING
You can’t use USING when the join columns have different names
Example:
- Employee.empId
- Bonus.employee_id
Then USING(empId) won’t work because Bonus doesn’t have empId and you have to use ON instead:
You also can’t use USING when your join condition isn’t a simple equal columns
Examples:
- Joining on multiple conditions with different names
- Inequalities (>=, BETWEEN, ranges)
- Joining on expressions (DATE(e.created_at) = b.day)
Overall for me personally, USING feels more efficient as a writer/reader:
- less typing
- fewer places to make mistakes
- cleaner output (no duplicate join key columns)