EN
Node.js - MySQL Inner Join
0 points
In this article, we would like to show you how to use MySQL INNER JOIN in Node.js.
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
'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
(5, 'Operations', 'Boston');9
10
INSERT INTO `users`11
( `name`, `surname`, `department_id`)12
VALUES13
('John', 'Stewart', 1),14
('Chris', 'Brown', 2),15
('Kate', 'Lewis', 3),16
('Ailisa', 'Gomez', NULL),17
('Gwendolyn', 'James', 2),18
('Simon', 'Collins', 4),19
('Taylor', 'Martin', 2),20
('Andrew', 'Thompson', NULL);xxxxxxxxxx1
// ...2
connection.connect(error => {3
if (error) throw error;4
const query = `SELECT * 5
FROM ??6
JOIN ?? ON ??.?? = ??.??`;7
const values = [8
'users', // SELECT *9
'departments', // FROM `users`10
'departments', // JOIN `departments` 11
'id', // ON departments.id = users.department_id12
'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 * FROM ?? AS A5
JOIN ?? AS B ON A.?? = B.??`;6
const values = [7
'users', // SELECT * FROM `users` AS A8
'departments', // JOIN `departments` AS B ON B.id = A.department_id9
'users',10
'department_id',11
];12
13
connection.query(query, values, (error, result) => { // sends queries14
connection.end(); // closes connection15
if (error) throw error;16
console.log(result);17
});18
});