ON

MySQL

Example

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Output

+---------+---------------+
| order_id| customer_name |
+---------+---------------+
| 1       | John Doe      |
| 2       | Jane Doe      |
+---------+---------------+

Explanation

This SQL statement performs an INNER JOIN between the orders table and the customers table where the customer_id of both tables match. The result is a new table that shows the order_id from the orders table and the customer_name from the customers table for each matching customer_id.

PostgreSQL

Example

    CREATE TABLE employees (
        emp_id INT PRIMARY KEY,
        emp_name VARCHAR (50),
        dept_id INT
    );

    CREATE TABLE department (
        dept_id INT PRIMARY KEY,
        dept_name VARCHAR (50)
    );

    SELECT emp_name, dept_name
    FROM employees
    JOIN department
    ON employees.dept_id = department.dept_id;

Output

    emp_name | dept_name
    ------------+-----------
    John     | Sales
    Jane     | Marketing
    Bob      | HR

Explanation

The ON keyword in PostgreSQL is used in a JOIN clause to specify the conditions that should be met for records being joined. In the example, the employees table is being joined with the department table based on dept_id. This means for each employee, the SQL engine will match it to a department where the dept_id is the same, displaying the employee’s name (emp_name) and the corresponding department’s name (dept_name).

SQL Server

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Output

| OrderID | CustomerName |  OrderDate   |
|:-------:|:------------:|:------------:|
|   1     |  Customer A  | 01-07-2021   |
|   2     |  Customer B  | 02-07-2021   |
|   3     |  Customer A  | 04-07-2021   |

Explanation

In the provided SQL code, the ON keyword is used to specify the field upon which the INNER JOIN is to be performed. This code gets the OrderID, CustomerName, and OrderDate from two tables: Orders and Customers. The join is performed on the basis of CustomerID which is common in both tables. The output returns a consolidated view of all orders along with the corresponding customer name and order date.

Oracle

Example

SELECT EMPLOYEES.FIRST_NAME, ORDERS.ORDER_DATE
FROM EMPLOYEES
JOIN ORDERS
ON EMPLOYEES.ORDER_ID = ORDERS.ID;

Output

FIRST_NAME   ORDER_DATE
----------   ----------
John         01-Jan-2021
Jane         05-Feb-2021

Explanation

In the example provided, the SQL JOIN statement is utilized in conjunction with the ON keyword to combine rows from both the EMPLOYEES and ORDERS tables based on a common column between them, which, in this case, is the ORDER_ID. The result is a newly formed table that includes the FIRST_NAME from the EMPLOYEES table and ORDER_DATE from the ORDERS table.

SQLite

Example

CREATE TABLE Employees (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    DepartmentID INTEGER
);

CREATE TABLE Departments (
    DepartmentID INTEGER PRIMARY KEY,
    DepartmentName TEXT
);

INSERT INTO Employees (Name, DepartmentID) VALUES ('John', 1), ('Jane', 2);

INSERT INTO Departments (DepartmentName) VALUES ('Sales'), ('Marketing');

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
ON Employees.DepartmentID = Departments.DepartmentID;

Output

Name   DepartmentName
------ -------------
John   Sales
Jane   Marketing

Explanation

In this example, we’ve created two tables - Employees and Departments. Using the ON statement, the SELECT query is able to join the two tables on the DepartmentID field. This allows us to get the Name from the Employees table and DepartmentName from the Departments table in a single query.