INNER_JOIN

Inner Join

Filtering a cross join by a condition

An inner join operation returns the join of two tables using a condition to pick rows from the cross join. In SQL this condition is specified with an ON clause.

For example, take the following two tables:

employee
+------+--------+-----------------+
| id   | name   | department_id   |
|------+--------+-----------------|
| 1    | Josh   | 1               |
| 2    | Ruth   | 2               |
| 3    | Greg   | <null>          |
+------+--------+-----------------+

department
+------+-------------+
| id   | name        |
|------+-------------|
| 1    | Sales       |
| 2    | Marketing   |
| 3    | Engineering |
+------+-------------+

Note that the employee table has an entry (Greg) with a null department_id and the department table has a department (Engineering) which is not referenced by any employee.

PostgeSQL example

In PostgreSQL an INNER JOIN on these tables looks like:

SELECT * FROM employee JOIN department ON department.id = employee.department_id;
+------+--------+-----------------+------+-----------+
| id   | name   | department_id   | id   | name      |
|------+--------+-----------------+------+-----------|
| 1    | Josh   | 1               | 1    | Sales     |
| 2    | Ruth   | 2               | 2    | Marketing |
+------+--------+-----------------+------+-----------+

The ON clause is used to specify that we only want to see the records from the cross join where the employee's department id matches the department id.

JSDB example

This same call in our database:

employee = FROM('employee');
department = FROM('department');
result = INNER_JOIN( employee, department, (c) => c["employee.department_id"] === c["department.id"] );
table(result);

We load the two tables into variables and pass them to INNER_JOIN which applies CROSS_JOIN and then filters the result by the condition. The condition requires the column employee.department_id to equal department.id.

The result is:

┌─────────────┬───────────────┬────────────────────────┬───────────────┬─────────────────┐
│ employee.id │ employee.name │ employee.department_id │ department.id │ department.name │
├─────────────┼───────────────┼────────────────────────┼───────────────┼─────────────────┤
│      1      │     Josh      │           1            │       1       │      Sales      │
│      2      │     Ruth      │           2            │       2       │    Marketing    │
└─────────────┴───────────────┴────────────────────────┴───────────────┴─────────────────┘

INNER_JOIN Implementation

INNER_JOIN(a,b, pred) takes two tables and a predicate. The result will be a table which includes the cross join of all rows which satisfy the predicate. The predicate has the signature (a,b) => boolean, taking two rows as input and returning true for rows which should be in the resulting table.

const INNER_JOIN = (a, b, pred) => {
  return {
    name: '',
    rows: CROSS_JOIN(a, b).rows.filter(pred),
  }
};

The implementation returns a new table with rows from the cross join filtered using the standard Array.prototype.filter() method.