SQL ON

What does the SQL ON clause do?

Defines the condition or target object for JOINs, MERGE statements, triggers, and foreign-key actions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ON: ANSI/ISO SQL, PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, SQLite, DB2, Redshift, BigQuery

SQL ON Full Explanation

ON is a core SQL keyword that links two datasets or binds an object to an event. In a JOIN, ON introduces the Boolean predicate that determines which rows from each table are combined. In MERGE, it defines the match criteria between the target and source tables. For triggers, ON specifies the table that fires the trigger. In foreign-key definitions, ON precedes cascading actions such as ON DELETE CASCADE or ON UPDATE SET NULL. The ON clause is evaluated after the Cartesian product of the joined tables is formed (except for semi-joins such as LEFT JOIN where filtering rules differ). Because the ON predicate is evaluated before the WHERE clause, it can influence whether unmatched rows survive in outer joins. ON supports any valid Boolean expression, including comparisons, IS NULL checks, subqueries, and complex logical operators. Although ON is widely supported, specific extensions (e.g., INSERT ... ON CONFLICT in PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL) are dialect-specific and not part of the ANSI definition.

SQL ON Syntax

-- JOIN
SELECT column_list
FROM table1 [INNER|LEFT|RIGHT|FULL] JOIN table2
ON table1.col = table2.col;

-- MERGE
MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);

-- Trigger
CREATE TRIGGER trg_name
AFTER INSERT ON table_name
FOR EACH ROW EXECUTE PROCEDURE func();

-- Foreign key
ALTER TABLE child
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE;

SQL ON Parameters

search_condition (Boolean) - Expression evaluated for each row combination
object_name (Identifier) - Table or view to which a trigger is attached
cascade_action (Keyword) - Action executed on DELETE or UPDATE in a foreign key (CASCADE, SET NULL, etc.)

Example Queries Using SQL ON

-- 1. Equi-join using ON
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;

-- 2. Outer join where ON predicate controls null-padding
SELECT e.emp_id, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

-- 3. MERGE with ON match condition (SQL Server / Oracle)
MERGE INTO inventory tgt
USING staging src
ON (tgt.sku = src.sku)
WHEN MATCHED THEN UPDATE SET qty = src.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (src.sku, src.qty);

-- 4. Trigger attached with ON
CREATE TRIGGER log_insert
AFTER INSERT ON transactions
FOR EACH ROW CALL "log_txn";

Expected Output Using SQL ON

Returns each order with the matching customer.
Returns all employees; departments will be NULL where not matched.
Updates existing inventory rows or inserts new ones in a single statement.
Associates the trigger so every INSERT on transactions writes a log row.

Use Cases with SQL ON

Joining tables on a specific key instead of relying on implicit WHERE joins
Matching source and target rows during upsert operations with MERGE
Attaching triggers to table events (INSERT, UPDATE, DELETE)
Defining referential-integrity actions that execute when parent keys change

Common Mistakes with SQL ON

Placing filter logic in WHERE instead of ON for outer joins, unintentionally converting them to inner joins
Referencing columns without qualifying them, leading to ambiguous-column errors
Forgetting parentheses around the ON condition in MERGE (required in some dialects)
Assuming INSERT ... ON CONFLICT or ON DUPLICATE KEY is ANSI standard when they are vendor-specific

First Introduced In

SQL-92 (explicit JOIN...ON syntax)

Frequently Asked Questions

When should I write conditions in ON instead of WHERE?

Use ON for row-matching logic between tables. Use WHERE for post-join filtering. For outer joins, misplacing conditions in WHERE can eliminate the null-padded rows you expect.

Is ON mandatory for JOINs?

Yes for explicit JOIN syntax (INNER, LEFT, RIGHT, FULL). CROSS JOINs or the old comma join can omit it, but they are harder to read and often discouraged.

Does ON run before WHERE in the execution order?

Logically yes. The database evaluates the join predicate (ON) while forming the joined row set, then applies the WHERE filter. This order is crucial for understanding outer-join results.

Can I reuse ON in triggers and foreign keys?

Absolutely. ON also names the table that fires a trigger and precedes cascading actions in foreign-key constraints, such as ON DELETE CASCADE.