1. SQL Recipes
2. Advanced
3. Understanding how Joins work – examples with Javascript implementation

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.

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;

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;

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;

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 

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 

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]);
}