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:
-
Combine data: Join two or more tables to retrieve data from multiple sources and present it as a unified result set.
-
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.
-
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
andtable2
: 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
andtable2
: 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.