EN
Node.js - PostgreSQL Right Join
0
points
In this article, we would like to show you how to use SQL RIGHT JOIN in Node.js.
Note: at the end of this article you can find database preparation SQL queries.
const { Client } = require('pg');
const client = new Client({
host: '127.0.0.1',
user: 'postgres',
database: 'database_name',
password: 'password',
port: 5432,
});
const fetchUserDepartments = async () => {
const query = `SELECT *
FROM "users"
RIGHT JOIN "departments" ON "departments"."id" = "users"."department_id"`;
try {
await client.connect(); // gets connection
const { rows } = await client.query(query); // sends queries
console.log(rows);
} catch (error) {
console.error(error.stack);
} finally {
await client.end(); // closes connection
}
};
fetchUserDepartments();
![Tables in the database - HeidiSQL](/static/bucket/1618841883139-rdaAo5qYG6--image.png)
Output:
![Result of the above query - HeidiSQL](/static/bucket/1617738883292-BZOQxdZQ3p--image.png)
Database preparation
create_tables.sql
file:
CREATE TABLE "departments" (
"id" SERIAL,
"department_name" VARCHAR(50) NOT NULL,
"location" VARCHAR(50) NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "users" (
"id" SERIAL,
"name" VARCHAR(50) NOT NULL,
"surname" VARCHAR(50) NOT NULL,
"department_id" INTEGER,
PRIMARY KEY ("id"),
FOREIGN KEY ("department_id") REFERENCES "departments" ("id")
);
insert_data.sql
file:
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);