Node.js - MySQL FULL OUTER Join

const mysql = require('mysql');

​

const connection = mysql.createConnection({        // gets connection with database

    host: 'localhost', // '127.0.0.1'

    user: 'root',

    password: 'password',

    database: 'my_database',

});

​

connection.connect((error) => {

    if (error) throw error;

    const query = 'SELECT * ' +

                  'FROM `users` ' +

                  'LEFT JOIN `departments` ON `departments`.id = `users`.department_id ' +

                  'UNION ' +

                  'SELECT * ' +

                  'FROM `users` ' +

                  'RIGHT JOIN `departments` ON `departments`.id = `users`.department_id';

​

    connection.query(query, (error, result) => {   // sends queries

        connection.end();                          // closes connection

        if (error) throw error;

        console.log(result);

    });

});

CREATE TABLE `departments` (

    `id` INT(10) UNSIGNED NOT NULL,

    `department_name` VARCHAR(50) NOT NULL,

    `location` VARCHAR(50) NULL,

    PRIMARY KEY (`id`)

);

​

CREATE TABLE `users` (

    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(50) NOT NULL,

    `surname` VARCHAR(50) NOT NULL,

    `department_id` INT(10) UNSIGNED,

    PRIMARY KEY (`id`),

    FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)

)

INSERT INTO `departments`

    (`id`, `department_name`, `location`)

VALUES

    (1, 'Sales', 'New York'),

    (2, 'Finance', NULL),

    (3, 'HR', 'Atlanta'),

    (4, 'Purchase', 'New Orlean'),

    (5, 'Operations', 'Boston');

​

INSERT INTO `users`

    ( `name`, `surname`, `department_id`)

VALUES

    ('John', 'Stewart', 1),

    ('Chris', 'Brown', 2),

    ('Kate', 'Lewis', 3),

    ('Ailisa', 'Gomez', NULL),

    ('Gwendolyn', 'James', 2),

    ('Simon', 'Collins', 4),

    ('Taylor', 'Martin', 2),

    ('Andrew', 'Thompson', NULL);