JoinsType - Join Operations with ON Relation

Explanation of ON and table Usage

When constructing SQL joins, the ON condition is used to define how two tables are related by specifying which columns should be matched. In some cases, you can use the table parameter (which refers to the table being joined) along with the specific column name to define the join conditions more clearly.

When using the ON relation, you need to explicitly mention both the table name and column name to establish the join condition. You can also optionally specify an operator (default is '=') to control how the columns are compared in the join condition.

Example 1: Using ON with Table and Column

Here’s an example where we perform an INNER JOIN between the orders and customers tables using the customer_id field to match the related records in both tables.

const joins: JoinsType<['orders', 'customers']> = [
  {
    type: 'INNER JOIN',
    table: 'customers',  // The table to join with 'orders'
    on: 'orders.customer_id = customers.id'  // Join condition where customer_id matches
  }
];

console.log(joins);
// Output: [
//   { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
// ]

• ON Relation: The ON condition defines how the tables should be related. Here, it specifies that the customer_id column in the orders table should be matched with the id column in the customers table.

Example 2: Multiple Joins with ON and table

In this example, we perform both an INNER JOIN between orders and customers, and a LEFT JOIN between orders and payments. Notice how each join uses the ON relation with table and column names specified.

const joins: JoinsType<['orders', 'customers', 'payments']> = [
  {
    type: 'INNER JOIN',
    table: 'customers',  // First table to join
    on: 'orders.customer_id = customers.id'  // ON relation between orders and customers
  },
  {
    type: 'LEFT JOIN',
    table: 'payments',  // Second table to join
    on: 'orders.id = payments.order_id'  // ON relation between orders and payments
  }
];

console.log(joins);
// Output: [
//   { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
//   { type: 'LEFT JOIN', table: 'payments', on: 'orders.id = payments.order_id' }
// ]

Here we have two joins:

1. An INNER JOIN between orders and customers where the customer_id field from orders matches the id field from customers.
2. A LEFT JOIN between orders and payments, where orders.id matches payments.order_id.

Example 3: Using operator and Custom Column Names

The operator field is optional and can be used to specify advanced join conditions. If not specified, the default operator is '='. Here's an example where we specify an operator for the join condition, and custom column names for matching the customer_id field.

const joins: JoinsType<['orders', 'customers']> = [
  {
    type: 'INNER JOIN',
    operator: '=',  // Using the default operator '='
    customer: 'customer_id', // First table (orders) column name
    order: 'customer_id'     // Second table (customers) column name
  }
];

console.log(joins);
// Output: [
//   { type: 'INNER JOIN', operator: '=', customer: 'customer_id', order: 'customer_id' }
// ]

In this case, the join will occur where the customer_id in the orders table is equal to the customer_id in the customers table, with the default = operator.

Example 4: Using JoinsType for Deletion Operations

For deletion or filtering of rows, you might want to filter specific records based on a join. Here's an example where we are using the JoinsType to filter rows for deletion operations by joining the orders table with the customers table.

const joins: JoinsType<['orders', 'customers']> = [
  {
    type: 'INNER JOIN', // The type of join
    operator: '=',  // The operator (default: '=')
    customer: 'customer_id', // Primary table field
    order: 'customer_id' // Foreign table field
  }
];

console.log(joins);
// Output: [
//   { type: 'INNER JOIN', operator: '=', customer: 'customer_id', order: 'customer_id' }
// ]

Here, we are performing an INNER JOIN on the customer_id field between orders and customers to filter records for deletion. The operator is set to = (which is the default).

Example 5: Using JoinsType with Multiple Tables and Conditions

Let's consider an example where we want to perform multiple join operations with a combination of INNER JOIN and LEFT JOIN between orders, customers, and payments, but we want to explicitly specify column names using the on relation.

const joins: JoinsType<['orders', 'customers', 'payments']> = [
  {
    type: 'INNER JOIN',
    table: 'customers',
    on: 'orders.customer_id = customers.id' // Join condition
  },
  {
    type: 'LEFT JOIN',
    table: 'payments',
    on: 'orders.id = payments.order_id' // Join condition
  }
];

console.log(joins);
// Output: [
//   { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
//   { type: 'LEFT JOIN', table: 'payments', on: 'orders.id = payments.order_id' }
// ]

This example shows how you can combine different types of joins (INNER and LEFT) in a single query. Each join condition is specified using the on field, with the appropriate table and column names.

Conclusion

The JoinsType structure allows for flexible SQL join operations where:

1. The type field defines the join type.
2. The table field specifies the table being joined.
3. The on field defines the condition (with table and column names) for the join.

When working with joins, it's important to use the on relation with specific table-column pairs to ensure clarity and correctness in how the tables are joined. Additionally, using the operator field can further refine how columns are compared in the join condition.