Other Join Types - Learning Module

Loading content...

0/241

USING Clause: Explicit Column-Based Joins

The Best of Both Worlds

What if you could have the conciseness and automatic column de-duplication of NATURAL JOIN, but with explicit control over which columns are used for the join condition? The USING clause delivers exactly this balance.

USING sits between the full automation of NATURAL JOIN (dangerous) and the verbosity of ON clause (verbose but safe). It requires you to explicitly specify which columns to join on, making your intent clear and your queries immune to schema evolution—while still providing the convenient column merging behavior that NATURAL JOIN offers.

Understanding USING is essential for writing clean, maintainable SQL that avoids redundant columns in result sets without sacrificing safety.

What You Will Learn

By the end of this page, you will master USING clause syntax and semantics, understand its column de-duplication behavior, know when to choose USING over ON, recognize database-specific variations and limitations, and apply USING effectively in multi-table joins.

Syntax and Semantics

The USING clause provides a shorthand for equi-joins where the join columns have identical names in both tables.

Basic Syntax:

SELECT columns
FROM table1
JOIN table2 USING (column_name)

Or for multiple columns:

SELECT columns
FROM table1
JOIN table2 USING (column1, column2, ...)

Key Requirements:

Column names must be identical in both tables
Column data types must be compatible (implicitly convertible for comparison)
The specified column appears exactly once in the result
Only equi-join conditions can be expressed (column1 = column1)

using_basic_syntax.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Sample tables
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    manager_id INT,
    hire_date DATE
);
 
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location_id INT
);
 
CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    city VARCHAR(100),
    country VARCHAR(100)
);
 
-- Single column USING
SELECT employee_id, employee_name, department_name
FROM employees
JOIN departments USING (department_id);
 
-- Equivalent ON clause version:
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 
-- Note: With USING, we don't need table aliases for department_id
-- because it appears only once in the result

The De-duplication Difference:

The critical distinction between USING and ON is how they handle the join column(s) in the result:

using_vs_on_dedup.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- With USING: column appears ONCE
SELECT *
FROM employees
JOIN departments USING (department_id);
 
-- Result columns:
-- department_id | employee_id | employee_name | hire_date | department_name | location_id
-- (department_id is NOT repeated)
 
-- With ON: column appears TWICE (unless manually excluded)
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 
-- Result columns:
-- employee_id | employee_name | department_id | hire_date | department_id | department_name | location_id
-- (department_id appears twice: once from each table)
 
-- To avoid duplication with ON, you must explicitly select:
SELECT e.employee_id, e.employee_name, e.department_id, 
       e.hire_date, d.department_name, d.location_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

USING vs ON: Key Differences
Aspect	USING (column)	ON (condition)
Column name requirement	Must be identical in both tables	Names can differ
Join column in result	Appears once (de-duplicated)	Appears twice (unless excluded)
Table alias for join column	Not needed (unqualified)	Required to distinguish
Condition complexity	Equality only	Any boolean expression
Multiple conditions	List columns: (c1, c2)	Combine with AND
Non-equi conditions	Not possible	Fully supported
Schema sensitivity	None (explicit)	None (explicit)

Column Ordering with USING

When using SELECT *, the USING column typically appears first in the result set, positioned before columns unique to either table. This is part of the SQL standard, though some databases may vary in exact ordering. For predictable results, always specify columns explicitly rather than relying on SELECT *.

Multi-Column USING

When tables share multiple columns that should all participate in the join condition, USING accepts a comma-separated list. This is particularly useful for compound key relationships.

Syntax for Multiple Columns:

multi_column_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Tables with composite key relationship
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);
 
CREATE TABLE shipment_items (
    order_id INT,
    product_id INT,
    ship_date DATE,
    tracking_number VARCHAR(50),
    PRIMARY KEY (order_id, product_id)
);
 
-- Multi-column USING: joins on BOTH columns
SELECT order_id, product_id, quantity, ship_date, tracking_number
FROM order_items
JOIN shipment_items USING (order_id, product_id);
 
-- Equivalent with ON clause:
SELECT oi.order_id, oi.product_id, oi.quantity, 
       si.ship_date, si.tracking_number
FROM order_items oi
JOIN shipment_items si 
    ON oi.order_id = si.order_id 
    AND oi.product_id = si.product_id;
 
-- All listed columns:
-- - Must exist in both tables
-- - Are de-duplicated (appear once each)
-- - All form the join condition (AND'd together)

Order Doesn't Matter in USING:

Unlike ON clauses where condition order can sometimes affect readability, the columns in USING can be listed in any order—the semantic meaning is identical:

using_column_order.sql
SQL
1
2
3
4
5
6
7
8
9
-- These are semantically identical:
SELECT * FROM t1 JOIN t2 USING (a, b, c);
SELECT * FROM t1 JOIN t2 USING (b, c, a);
SELECT * FROM t1 JOIN t2 USING (c, a, b);
 
-- However, some databases order result columns based on
-- the order specified in USING. For predictability, 
-- list columns in a consistent order (e.g., alphabetically
-- or matching the primary key definition).

Partial Key Joins

You can use USING with only a subset of common columns. If tables share columns (a, b, c) but you only want to join on (a, b), specify just those. This gives you more control than NATURAL JOIN while remaining more concise than ON for simple equi-joins.

partial_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Tables share multiple columns
CREATE TABLE monthly_sales (
    year INT,
    month INT,
    region_id INT,
    product_id INT,
    sales_amount DECIMAL(12,2)
);
 
CREATE TABLE monthly_targets (
    year INT,
    month INT,
    region_id INT,
    target_amount DECIMAL(12,2)
);
 
-- Join on year and month only (not region_id)
-- Compare ALL regions' sales to region-agnostic targets
SELECT m.year, m.month, m.region_id, m.product_id,
       m.sales_amount, t.target_amount
FROM monthly_sales m
JOIN monthly_targets t USING (year, month);
 
-- Or join on all three matching columns
-- Compare each region's sales to that region's target
SELECT year, month, region_id, product_id,
       sales_amount, target_amount
FROM monthly_sales
JOIN monthly_targets USING (year, month, region_id);

USING with All Join Types

The USING clause works with all major join types—INNER, LEFT, RIGHT, and FULL OUTER. The join type determines which unmatched rows are preserved; USING specifies the matching condition.

INNER JOIN with USING:

using_inner_join.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- INNER JOIN: Only matching rows
-- (JOIN and INNER JOIN are equivalent)
SELECT department_id, employee_name, department_name
FROM employees
JOIN departments USING (department_id);
 
-- Or explicitly:
SELECT department_id, employee_name, department_name
FROM employees
INNER JOIN departments USING (department_id);
 
-- Result: Employees who have a matching department
-- Employees with NULL or non-existent department_id excluded
-- Departments with no employees excluded

LEFT JOIN with USING:

using_left_join.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- LEFT JOIN: All left rows, matched right rows
SELECT department_id, employee_name, department_name
FROM employees
LEFT JOIN departments USING (department_id);
 
-- Result: ALL employees
-- - Employees with matching dept: show department_name
-- - Employees with NULL/missing dept: department_name is NULL
 
-- Use case: Find employees without departments
SELECT employee_id, employee_name
FROM employees
LEFT JOIN departments USING (department_id)
WHERE department_name IS NULL;

RIGHT JOIN with USING:

using_right_join.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- RIGHT JOIN: All right rows, matched left rows
SELECT department_id, employee_name, department_name
FROM employees
RIGHT JOIN departments USING (department_id);
 
-- Result: ALL departments
-- - Departments with employees: show employee data
-- - Departments without employees: employee columns are NULL
 
-- Use case: Find departments with no employees
SELECT department_id, department_name
FROM employees
RIGHT JOIN departments USING (department_id)
WHERE employee_name IS NULL;
 
-- Note: Many prefer converting RIGHT JOIN to LEFT JOIN
-- by swapping table order (more intuitive for some readers):
SELECT department_id, department_name
FROM departments
LEFT JOIN employees USING (department_id)
WHERE employee_name IS NULL;

FULL OUTER JOIN with USING:

using_full_outer_join.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- FULL OUTER JOIN: All rows from both tables
SELECT department_id, employee_name, department_name
FROM employees
FULL OUTER JOIN departments USING (department_id);
 
-- Result: ALL employees AND ALL departments
-- - Matched pairs: complete data
-- - Employees without dept: department_name is NULL
-- - Departments without employees: employee columns are NULL
 
-- Use case: Reconciliation report
SELECT 
    COALESCE(e.employee_id::text, 'NO EMPLOYEE') AS employee,
    department_id,
    COALESCE(d.department_name, 'NO DEPARTMENT') AS department
FROM employees e
FULL OUTER JOIN departments d USING (department_id);
 
-- Note: MySQL doesn't support FULL OUTER JOIN directly
-- Simulate with UNION of LEFT and RIGHT JOINs

USING Behavior by Join Type
Join Type	Left Table Rows	Right Table Rows	Result
INNER JOIN USING	Only matched	Only matched	Matched pairs only
LEFT JOIN USING	All	Only matched	All left + matched right
RIGHT JOIN USING	Only matched	All	Matched left + all right
FULL OUTER JOIN USING	All	All	All from both tables

Multi-Table Joins with USING

USING excels in multi-table join chains where foreign key columns share names with their referenced primary keys—a common pattern in well-designed schemas.

Chaining Multiple USING Clauses:

multi_table_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Well-designed schema with consistent naming
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE
);
 
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT,
    quantity INT
);
 
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    unit_price DECIMAL(10,2)
);
 
-- Multi-table join with USING: clean and readable
SELECT customer_name, order_date, product_name, quantity
FROM customers
JOIN orders USING (customer_id)
JOIN order_items USING (order_id)
JOIN products USING (product_id);
 
-- Compare to equivalent with ON clauses:
SELECT c.customer_name, o.order_date, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;
 
-- The USING version is more concise and
-- joins on exactly the intended relationships

Mixing USING and ON in the Same Query:

You can use USING for some joins and ON for others in a single query. This is useful when some relationships have matching column names and others don't:

mixing_using_on.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Mixed USING and ON
SELECT e.employee_name, d.department_name, m.employee_name AS manager_name
FROM employees e
JOIN departments d USING (department_id)  -- Same column name
JOIN employees m ON e.manager_id = m.employee_id;  -- Different names
 
-- The self-join on employees requires ON because
-- we're joining manager_id to employee_id (different names)
 
-- Another example: complex multi-source join
SELECT 
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity,
    s.supplier_name
FROM orders o
JOIN customers c USING (customer_id)           -- USING: same names
JOIN order_items oi USING (order_id)           -- USING: same names  
JOIN products p USING (product_id)             -- USING: same names
JOIN suppliers s ON p.primary_supplier = s.supplier_id;  -- ON: different names

Schema Design for USING

Schemas that consistently name foreign key columns to match their referenced primary keys (customer_id → customer_id, not cust_id or fk_customer) are USING-friendly. This naming convention improves readability and enables concise multi-table joins. Consider this when designing new schemas.

Database-Specific Behavior

While USING is part of the SQL standard, implementations vary across database systems. Understanding these differences is crucial for portable code.

PostgreSQL:

postgres_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- PostgreSQL: Full USING support
-- USING column cannot be table-qualified
SELECT department_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);
-- Works: department_id is unqualified
 
SELECT e.department_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);
-- ERROR: column "department_id" cannot be used with a table qualifier
-- in the USING clause result
 
-- PostgreSQL places USING columns first in SELECT *
SELECT * FROM employees JOIN departments USING (department_id);
-- department_id | employee_id | employee_name | ... | department_name | ...

MySQL:

mysql_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- MySQL: Full USING support
-- Similar to PostgreSQL, USING columns appear once
 
SELECT department_id, employee_name, department_name
FROM employees
JOIN departments USING (department_id);
 
-- MySQL allows table-qualified USING columns in some contexts
-- but not in the SELECT list when using *
 
-- MySQL-specific: USING with DELETE
DELETE FROM t1
USING t1 JOIN t2 USING (id)
WHERE t2.status = 'inactive';
 
-- Note: This USING is different from join USING!
-- It specifies which table to delete from in multi-table DELETE

Oracle:

oracle_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Oracle: Full USING support (SQL-standard compliant)
SELECT department_id, employee_name, department_name
FROM employees
JOIN departments USING (department_id);
 
-- Oracle-specific behavior:
-- USING columns must NOT be qualified with table names
-- in SELECT, WHERE, or any other clause
 
-- This works:
SELECT department_id, employee_name
FROM employees
JOIN departments USING (department_id)
WHERE department_id = 10;
 
-- This fails:
SELECT employees.department_id  -- ERROR
FROM employees
JOIN departments USING (department_id);

SQL Server:

sqlserver_using.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- SQL Server: USING clause NOT SUPPORTED
-- Must use ON clause instead
 
-- This PostgreSQL/MySQL/Oracle query:
SELECT department_id, employee_name, department_name
FROM employees
JOIN departments USING (department_id);
 
-- Must be written in SQL Server as:
SELECT e.department_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 
-- Or for de-duplication effect:
SELECT 
    COALESCE(e.department_id, d.department_id) AS department_id,
    e.employee_name, 
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

USING Clause Support by Database
Database	USING Support	Table Qualifier Allowed	Notes
PostgreSQL	Full	No (error if qualified)	USING columns first in SELECT *
MySQL	Full	Partially (context-dependent)	Also has USING in DELETE syntax
Oracle	Full	No (error if qualified)	Fully SQL-standard
SQL Server	NOT SUPPORTED	N/A	Use ON clause instead
SQLite	Full	No	Standard behavior

Portability Consideration

If your code must run on SQL Server, you cannot use USING. For maximum portability across all major databases, ON clause is the safe choice. However, if you're committed to PostgreSQL, MySQL, or Oracle, USING provides cleaner syntax for equi-joins on identically-named columns.

Common Patterns and Best Practices

USING clause shines in specific scenarios. Recognizing these patterns helps you decide when to apply it.

Pattern 1: Star Schema Joins

pattern_star_schema.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Data warehouse star schema: fact + dimensions
-- Dimension keys consistently named
 
SELECT 
    d.date_full,
    p.product_name,
    c.customer_segment,
    g.city,
    SUM(f.quantity) AS total_quantity,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key)
JOIN dim_customer c USING (customer_key)
JOIN dim_geography g USING (geography_key)
WHERE d.year = 2024
GROUP BY d.date_full, p.product_name, c.customer_segment, g.city;
 
-- Clean, readable multi-dimension join
-- Dimension keys appear once (de-duplicated)

Pattern 2: Hierarchical Lookups

pattern_hierarchy.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Geographic hierarchy with consistent naming
CREATE TABLE continents (
    continent_id INT PRIMARY KEY,
    continent_name VARCHAR(50)
);
 
CREATE TABLE countries (
    country_id INT PRIMARY KEY,
    continent_id INT,
    country_name VARCHAR(100)
);
 
CREATE TABLE regions (
    region_id INT PRIMARY KEY,
    country_id INT,
    region_name VARCHAR(100)
);
 
CREATE TABLE cities (
    city_id INT PRIMARY KEY,
    region_id INT,
    city_name VARCHAR(100),
    population INT
);
 
-- Full hierarchy traversal with USING
SELECT continent_name, country_name, region_name, city_name, population
FROM continents
JOIN countries USING (continent_id)
JOIN regions USING (country_id)
JOIN cities USING (region_id)
WHERE population > 1000000
ORDER BY population DESC;

Pattern 3: Temporal Tables

pattern_temporal.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Matching current and historical data
CREATE TABLE products_current (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    current_price DECIMAL(10,2)
);
 
CREATE TABLE products_history (
    product_id INT,
    effective_date DATE,
    historical_price DECIMAL(10,2),
    PRIMARY KEY (product_id, effective_date)
);
 
-- Compare current to historical
SELECT product_id, product_name, 
       current_price,
       historical_price AS price_one_year_ago,
       current_price - historical_price AS price_change
FROM products_current
JOIN products_history USING (product_id)
WHERE effective_date = CURRENT_DATE - INTERVAL '1 year';

USING Best Practices

•Use consistent FK naming — Name foreign keys to match the referenced primary key (customer_id → customer_id)
•Choose USING for clean equi-joins — When column names match and you want de-duplication, USING is clearer
•Use ON for complex conditions — Range checks, OR conditions, or inequality joins require ON
•Use ON for different column names — When employee.manager_id references employee.employee_id, ON is required
•Don't mix for same join — Each join uses either USING or ON, not both
•Consider SQL Server portability — If code must run on SQL Server, avoid USING entirely
•Avoid table-qualifying USING columns — Most databases error on e.department_id when using USING

Summary and Key Takeaways

The USING clause occupies a valuable middle ground in SQL join syntax—more concise than ON for simple equi-joins, yet explicit and safe unlike NATURAL JOIN. Let's consolidate the key points:

Key Takeaways

•USING requires identically-named columns — You specify which columns to join on, but they must have the same name in both tables.
•Columns are de-duplicated — Join columns appear exactly once in the result, unlike ON which includes both copies.
•Works with all join types — INNER, LEFT, RIGHT, and FULL OUTER all support USING clause.
•Multiple columns supported — USING (col1, col2) joins on both columns with implicit AND.
•Schema-safe — Unlike NATURAL JOIN, only explicitly listed columns participate in the join condition.
•Cannot table-qualify USING columns — In most databases, referencing e.department_id is an error when department_id is in USING.
•SQL Server doesn't support USING — For cross-database portability, stick with ON clause.
•Ideal for well-designed schemas — Schemas with consistent FK naming patterns benefit most from USING.

Page Complete

You now understand the USING clause's syntax, behavior, and appropriate use cases. It's a valuable tool for writing clean, readable joins when column names match. In the next page, we'll explore non-equi joins—join conditions that use operators other than equality, enabling powerful range-based and inequality matching.