SQL Join tutorial with Javascript implementation and examples

SQL joins are fundamental to relational databases. Relational databases organize data into different tables that are related in some way. SQL joins are how you can pull data from two or more tables to appear as a single set of data.

We are going to take a different approach to understanding SQL joins – we do this by writing Javascript code that do the joining of data on the application level rather than inside the database if you were to use SQL. If you understand Javascript and want to learn SQL joins – this tutorial is for you!


Sample data

We've provided sample SQL code and data for you to follow along. Our sample data is organized into two tables: employee and department. The two tables are related via the department_id column.

name department_id
Alice 12
Bob 13
Chris 13
Dan 14
Eve NULL
department_id name
12 Sales
13 Marketing
14 Engineering
15 Accounting
16 Operations

SQL

create table department (
    department_id int primary key, 
    name varchar(20)
)

create table employee (
    name varchar(20), 
    department_id int references department(department_id)
)

insert into department values(12, 'Sales');
insert into department values(13, 'Marketing');
insert into department values(14, 'Engineering');
insert into department values(15, 'Accounting');
insert into department values(16, 'Operations');

insert into employee values('Alice', 12);
insert into employee values('Bob', 13);
insert into employee values('Chris', 13);
insert into employee values('Dan', 14);
insert into employee values('Eve', null);

Javascript

var employee = [
    {name: 'Alice', department_id: 12},
    {name: 'Bob', department_id: 13},
    {name: 'Chris', department_id: 13},
    {name: 'Dan', department_id: 14},
    {name: 'Eve', department_id: null}
];

var department = [
    {department_id: 12, name: 'Sales'},
    {department_id: 13, name: 'Marketing'},
    {department_id: 14, name: 'Engineering'},
    {department_id: 15, name: 'Accounting'},
    {department_id: 16, name: 'Operations'}
];

Introduction: Types of SQL Joins

We have three types of joins available for use in SQL:

  1. Cross Join
  2. Inner Join
  3. Outer Join
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join

1. SQL Cross Join

The Cross Join returns the Cartesian product of rows from tables in the join – an exhaustive list of each row from the first table with each row from the second table. If the first table has rows and the second table has rows, then the result with the cross join will contain rows.

select * 
from 
employee 
cross join 
department;

The cross join can also be implicit:

select * 
from
employee, department;
name employee.department_id department.department_id department.name
Alice 12 12 Sales
Alice 12 13 Marketing
Alice 12 14 Engineering
Alice 12 15 Accounting
Alice 12 16 Operations
... ... ... ...

The resulting table will have 5 rows from employee times 5 rows from department, equals 25 rows in total.

Cross Join in Javascript

var results = [];
for (var i=0; i<employee.length; i++) {
    for (var j=0; j<department.length; j++) {
        results.push({
            employee_name: employee[i].name, 
            employee_department_id: employee[i].department_id,
            department_id: department[j].department_id,
            department_name: department[j].name
        });
    }
}

2. SQL Inner Join

The inner join is the simplest join which matches data based on the equality condition specified in the query. The word inner is optional.

select * 
from 
employee 
inner join 
department 
on employee.department_id = department.department_id;
name employee.department_id department.department_id department.name
Alice 12 12 Sales
Bob 13 13 Marketing
Chris 13 13 Marketing
Dan 14 14 Engineering

Inner Join in Javascript

var results = [];
for (var i=0; i<employee.length; i++) {
    for (var j=0; j<department.length; j++) {
        if (employee[i].department_id === department[j].department_id) {
            results.push({
                employee_name: employee[i].name, 
                employee_department_id: employee[i].department_id,
                department_id: department[j].department_id,
                department_name: department[j].name
            });
        }
    }
}

3. SQL Outer Join

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

3a. Left Outer Join

The left outer join returns a result table with the union of:

  • the matched data of two tables
  • remaining rows of the left table and null for all of the right table's columns.
select * 
from employee
left outer join 
department 
on employee.department_id = department.department_id;
name employee.department_id department.department_id department.name
Alice 12 12 Sales
Bob 13 13 Marketing
Chris 13 13 Marketing
Dan 14 14 Engineering
Eve null null null

Left Outer Join in Javascript

var results = [];
for (var i=0; i<employee.length; i++) {
    var found = false;
    for (var j=0; j<department.length; j++) {
        if (employee[i].department_id === department[j].department_id) {
            results.push({
                employee_name: employee[i].name, 
                employee_department_id: employee[i].department_id,
                department_id: department[j].department_id,
                department_name: department[j].name
            });
            found = true;
            break;
        }
    }
    if (found === false) {
        results.push({
            employee_name: employee[i].name, 
            employee_department_id: employee[i].department_id,
            department_id: null,
            department_name: null
        });    
    }
}

3b. Right Outer Join

The right outer join returns a result table with the union of:

  • the matched data of two tables
  • remaining rows of the right table and null for all of the left table's columns.
select * from employee
right outer join 
department 
on employee.department_id = department.department_id 
name employee.department_id department.department_id department.name
Alice 12 12 Sales
Bob 13 13 Marketing
Chris 13 13 Marketing
Dan 14 14 Engineering
null null 15 Accounting
null null 16 Operations

Right Outer Join in Javascript

var results = [];
for (var i=0; i<department.length; i++) {
    var found = false;
    for (var j=0; j<employee.length; j++) {
        if (employee[j].department_id === department[i].department_id) {
            results.push({
                employee_name: employee[j].name, 
                employee_department_id: employee[j].department_id,
                department_id: department[i].department_id,
                department_name: department[i].name
            });
            found = true;                
        }
    }
    if (found === false) {
        results.push({
            employee_name: null, 
            employee_department_id: null,
            department_id: department[i].department_id,
            department_name: department[i].name
        });    
    }
}

3c. Full Outer Join

The full outer join returns a result table with the union of:

  • matched data of two tables
  • remaining rows of both left table and then the right table.
select * from employee
full outer join 
department 
on employee.department_id = department.department_id 
name employee.department_id department.department_id department.name
Alice 12 12 Sales
Bob 13 13 Marketing
Chris 13 13 Marketing
Dan 14 14 Engineering
Eve null null null
null null 15 Accounting
null null 16 Operations

Full Outer Join in Javascript

Conceptually, the full outer join is the combination of applying both the left and right outer joins and removing the duplicates.

var results = [];
for (var i=0; i<employee.length; i++) {
    var found = false;
    for (var j=0; j<department.length; j++) {
        if (employee[i].department_id === department[j].department_id) {
            results.push({
                employee_name: employee[i].name, 
                employee_department_id: employee[i].department_id,
                department_id: department[j].department_id,
                department_name: department[j].name
            });
            found = true;
            break;
        }
    }
    if (found === false) {
        results.push({
            employee_name: employee[i].name, 
            employee_department_id: employee[i].department_id,
            department_id: null,
            department_name: null
        });    
    }
}
for (var i=0; i<department.length; i++) {
    var found = false;
    for (var j=0; j<employee.length; j++) {
        if (employee[j].department_id === department[i].department_id) {
            results.push({
                employee_name: employee[j].name, 
                employee_department_id: employee[j].department_id,
                department_id: department[i].department_id,
                department_name: department[i].name
            });
            found = true;                
        }
    }
    if (found === false) {
        results.push({
            employee_name: null, 
            employee_department_id: null,
            department_id: department[i].department_id,
            department_name: department[i].name
        });    
    }
}

// remove duplicates
for (var i=0; i< results.length; i++) {
    duplicates[JSON.stringify(results[i])] = results[i];
}

results = [];
for (var key in duplicates) {
    results.push(duplicates[key]);
}

👋 No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time. See past emails here.