EN
Node.js - MySQL Left Join
0 points
In this article, we would like to show you how to use SQL LEFT JOIN in Node.js.
Note: Note: at the end of this article you can find database preparation SQL queries, you will also find an example of a more parameterized query there.
xxxxxxxxxx1
const mysql = require('mysql');2
3
const connection = mysql.createConnection({ // gets connection with database4
host: 'localhost', // '127.0.0.1'5
user: 'root',6
password: 'password',7
database: 'my_database',8
});9
10
connection.connect(error => {11
if (error) throw error;12
const query = 'SELECT * ' +13
'FROM `users` ' +14
'LEFT JOIN `departments` ON `departments`.`id` = `users`.`department_id`';15
16
connection.query(query, (error, result) => { // sends queries17
connection.end(); // closes connection18
if (error) throw error;19
console.log(result);20
});21
});
Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `departments` (2
`id` INT(10) UNSIGNED NOT NULL,3
`department_name` VARCHAR(50) NOT NULL,4
`location` VARCHAR(50) NULL,5
PRIMARY KEY (`id`)6
);7
8
CREATE TABLE `users` (9
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,10
`name` VARCHAR(50) NOT NULL,11
`surname` VARCHAR(50) NOT NULL,12
`department_id` INT(10) UNSIGNED,13
PRIMARY KEY (`id`),14
FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)15
)insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `departments`2
(`id`, `department_name`, `location`)3
VALUES4
(1, 'Sales', 'New York'),5
(2, 'Finance', NULL),6
(3, 'HR', 'Atlanta'),7
(4, 'Purchase', 'New Orlean');8
9
INSERT INTO `users`10
( `name`, `surname`, `department_id`)11
VALUES12
('John', 'Stewart', 1),13
('Chris', 'Brown', 2),14
('Kate', 'Lewis', 3),15
('Ailisa', 'Gomez', NULL),16
('Gwendolyn', 'James', 2),17
('Simon', 'Collins', 4),18
('Taylor', 'Martin', 2),19
('Andrew', 'Thompson', NULL);xxxxxxxxxx1
// ...2
connection.connect((error) => {3
if (error) throw error;4
const query = `SELECT * 5
FROM ??6
LEFT JOIN ?? ON ??.?? = ??.??`;7
const values = [8
'users', // SELECT *9
'departments', // FROM `users`10
'departments', // LEFT JOIN `departments` ON departments.id = users.department_id11
'id',12
'users',13
'department_id',14
];15
16
connection.query(query, values, (error, result) => { // sends queries17
connection.end(); // closes connection18
if (error) throw error;19
console.log(result);20
});21
});The names of columns and tables can be placed in the query by a double question mark ??.
Another parameterized solution:
xxxxxxxxxx1
// ...2
connection.connect((error) => {3
if (error) throw error;4
const query = `SELECT * 5
FROM ?? AS A6
LEFT JOIN ?? AS B ON A.?? = B.??`;7
const values = [8
'users', // SELECT * 9
'departments', // FROM `users` AS A10
'users', // LEFT JOIN `departments` AS B ON B.`id` = A.`department_id`11
'department_id',12
];13
14
connection.query(query, values, (error, result) => { // sends queries15
connection.end(); // closes connection16
if (error) throw error;17
console.log(result);18
});19
});