SQL JOIN ON

What is SQL ON?

The SQL ON clause is used in conjunction with JOIN statements to specify the conditions that determine how two or more tables are related or joined. It specifies the columns from each table that are used to establish the join conditions, allowing you to connect rows based on related data.

When you would use it

You would use the SQL ON clause when you want to:

1. Combine data: Join two or more tables to retrieve data from multiple sources and present it as a unified result set.

2. Establish relationships: Define the conditions that determine how rows in one table relate to rows in another, such as connecting employees to their departments or orders to customers.

3. Filter data: Apply specific filtering criteria to the join, limiting the rows that are included in the result set.

Syntax

The syntax for using the ON clause with SQL JOIN statements is as follows:

SELECT column_list
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

• column_list: The columns you want to retrieve from the joined tables.
• table1 and table2: The names of the tables to be joined.
• column_name: The common column(s) or key(s) that are used to establish the relationship between the tables.

Parameter values

• column_list: A list of column names you want to select from the joined tables.
• table1 and table2: The names of the tables that you want to join.
• column_name: The column(s) that create the relationship between the tables. This can be one or more columns.

Example query

Suppose you have two tables, "employees" and "departments," and you want to retrieve a list of employees along with their corresponding departments. You can use the SQL ON clause in a JOIN operation like this:

SELECT employees.employee_name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

Example table response

Assuming the "employees" and "departments" tables contain the following data:

employees:

| employee_id | employee_name | department_id |
| ----------- | ------------- | ------------- |
| 1           | John Smith    | 101           |
| 2           | Mary Johnson  | 102           |
| 3           | Sam Brown     | 101           |

departments:

| department_id | department_name |
| ------------- | --------------- |
| 101           | HR              |
| 102           | Finance         |

The query mentioned earlier would return the following result:

| employee_name | department_name |
| ------------- | --------------- |
| John Smith    | HR              |
| Mary Johnson  | Finance         |
| Sam Brown     | HR              |

This result combines data from both tables, showing employees and their corresponding departments based on the relationship defined in the ON clause.

Use cases

• Combining data from multiple tables based on specific relationships or conditions.
• Retrieving data from related tables to create meaningful and informative result sets.
• Filtering data based on specific criteria when joining tables.

SQL languages this is available for

The ON clause is a standard SQL feature and is available in most relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of the ON clause are consistent across these database systems.