Chapter 63: Node.js MySQL Join
JOIN in MySQL from Node.js.
We are going to build this knowledge together, step by step, as if I am sitting next to you right now:
- I open MySQL Workbench (or terminal / DBeaver) on one side
- I open VS Code on the other side
- We type every SQL line and every line of Node.js code live
- I explain why we write it this way (performance, correctness, security, maintainability)
- I show what most beginners do wrong (very common and dangerous mistakes)
- I show what intermediate developers often forget (leads to wrong results or slow queries)
- I show what real production code looks like in 2025–2026
Goal of this lesson
Learn how to write correct, safe, performant, readable JOIN queries from Node.js to MySQL.
At the end you will know how to:
- Join two tables correctly
- Understand the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN
- Handle NULLs properly when using outer joins
- Filter with WHERE after JOIN
- Order & paginate joined results
- Avoid the most common performance & logic bugs
Step 1 – Project setup (minimal but complete)
If you don’t have the project yet:
Bash
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mkdir task-api-mysql-join cd task-api-mysql-join npm init -y npm pkg set type=module npm install express dotenv mysql2 zod npm install -D typescript tsx nodemon @types/express @types/node |
tsconfig.json (strict & modern)
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "compilerOptions": { "target": "ES2022", "module": "NodeNext", "moduleResolution": "NodeNext", "esModuleInterop": true, "forceConsistentCasingInFileNames": true, "strict": true, "skipLibCheck": true, "outDir": "./dist", "rootDir": "./src" }, "include": ["src/**/*"] } |
package.json scripts
JSON
|
0 1 2 3 4 5 6 7 8 9 |
"scripts": { "dev": "tsx watch src/index.ts", "start": "node dist/index.js" } |
src/config/database.ts (connection pool – production must-have)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import mysql from 'mysql2/promise' export const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'your-root-password', database: 'task_app', waitForConnections: true, connectionLimit: 10, queueLimit: 0, timezone: '+00:00', namedPlaceholders: true, multipleStatements: false }) async function checkConnection() { try { const conn = await pool.getConnection() console.log('MySQL pool ready') conn.release() } catch (err) { console.error('MySQL connection failed:', err) process.exit(1) } } checkConnection() |
Step 2 – Create test tables & data (run once in MySQL)
Open MySQL client and run:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
-- Create database if not exists CREATE DATABASE IF NOT EXISTS task_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE task_app; -- Users table CREATE TABLE IF NOT EXISTS users ( id CHAR(36) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Tasks table with foreign key CREATE TABLE IF NOT EXISTS tasks ( id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL, title VARCHAR(150) NOT NULL, priority ENUM('low', 'medium', 'high') DEFAULT 'medium', completed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); -- Insert test users INSERT IGNORE INTO users (id, email, name) VALUES ('user-1-uuid', 'aman@example.com', 'Aman'), ('user-2-uuid', 'priya@example.com', 'Priya'), ('user-3-uuid', 'rahul@example.com', 'Rahul'), ('user-4-uuid', 'sara@example.com', 'Sara'); -- Insert test tasks INSERT INTO tasks (id, user_id, title, priority, completed, created_at) VALUES (UUID(), 'user-1-uuid', 'Finish quarterly report', 'high', false, '2025-02-01 10:00:00'), (UUID(), 'user-1-uuid', 'Call client', 'medium', true, '2025-02-02 14:30:00'), (UUID(), 'user-2-uuid', 'Design new logo', 'high', false, '2025-02-03 09:15:00'), (UUID(), 'user-3-uuid', 'Update website footer', 'low', false, '2025-02-04 16:45:00'), (UUID(), 'user-3-uuid', 'Prepare presentation', 'high', false, '2025-02-05 11:20:00'); |
Note: We use INSERT IGNORE for users so we can run the script multiple times without duplicate errors.
Step 3 – Basic INNER JOIN (only matching records)
Goal: Show each task together with the user who created it.
src/controllers/task.controller.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
import { Request, Response } from 'express' import pool from '../config/database.js' export const getTasksWithUser = async (_req: Request, res: Response) => { try { const [rows] = await pool.execute(` SELECT t.id AS task_id, t.title, t.priority, t.completed, t.created_at AS task_created_at, u.id AS user_id, u.email, u.name FROM tasks t INNER JOIN users u ON t.user_id = u.id ORDER BY t.created_at DESC `) res.json({ success: true, count: (rows as any[]).length, data: rows }) } catch (err) { console.error('JOIN query error:', err) res.status(500).json({ success: false, message: 'Database error' }) } } |
Route
TypeScript
|
0 1 2 3 4 5 6 |
router.get('/tasks-with-users', getTasksWithUser) |
What you get back
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "success": true, "count": 5, "data": [ { "task_id": "uuid-...", "title": "Finish quarterly report", "priority": "high", "completed": false, "task_created_at": "2025-02-01T10:00:00.000Z", "user_id": "user-1-uuid", "email": "aman@example.com", "name": "Aman" }, // ... more rows ] } |
INNER JOIN vs LEFT JOIN
| Join type | What it returns | When to use it |
|---|---|---|
| INNER JOIN | Only rows that have matching record in both tables | You only want tasks that have a valid user |
| LEFT JOIN | All rows from left table (tasks), NULLs if no match | You want all tasks even if user was deleted |
Most common real-world mistake
SQL
|
0 1 2 3 4 5 6 7 |
-- Wrong: uses INNER JOIN but forgets some users have no tasks SELECT u.email, t.title FROM users u INNER JOIN tasks t ON u.id = t.user_id |
→ Users without tasks disappear from result!
Correct (when you want all users)
SQL
|
0 1 2 3 4 5 6 7 8 |
SELECT u.email, t.title FROM users u LEFT JOIN tasks t ON u.id = t.user_id |
Step 4 – LEFT JOIN + WHERE + ORDER BY + LIMIT (real paginated list)
src/controllers/task.controller.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
export const getPaginatedTasksWithUsers = async (req: Request, res: Response) => { try { // Pagination parameters – safe defaults const page = Math.max(1, Number(req.query.page) || 1) const limit = Math.min(50, Math.max(5, Number(req.query.limit) || 10)) const offset = (page - 1) * limit // Optional filter example const completed = req.query.completed let sql = ` SELECT t.id AS task_id, t.title, t.priority, t.completed, t.created_at AS task_created_at, u.id AS user_id, u.email, u.name FROM tasks t LEFT JOIN users u ON t.user_id = u.id WHERE 1=1 ` const params: Record<string, any> = {} if (completed !== undefined) { sql += ' AND t.completed = :completed' params.completed = completed === 'true' ? 1 : 0 } sql += ' ORDER BY t.created_at DESC' sql += ' LIMIT :limit OFFSET :offset' const [rows] = await pool.execute(sql, { ...params, limit, offset }) // Get total count (for pagination metadata) const [totalRows] = await pool.execute('SELECT COUNT(*) as total FROM tasks') const total = (totalRows as any[])[0].total res.json({ success: true, data: rows, pagination: { page, limit, total, totalPages: Math.ceil(total / limit), hasNext: page * limit < total, hasPrev: page > 1 } }) } catch (err) { next(err) } } |
Example URLs
- /api/tasks/paginated?page=1&limit=5
- /api/tasks/paginated?page=2&limit=10&completed=true
Why LEFT JOIN here?
We want all tasks, even if the user was deleted later. LEFT JOIN ensures we don’t lose tasks.
Why WHERE 1=1?
Makes adding AND conditions safe & clean — no need to check if it’s the first condition.
Step 5 – Summary – JOIN + WHERE + ORDER BY + LIMIT best practices
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Use named placeholders :name | Security + readability | WHERE user_id = :userId |
| Choose INNER vs LEFT JOIN wisely | INNER = only matching, LEFT = all from left table | Use LEFT when you need all records from main table |
| Always use ORDER BY | MySQL does NOT guarantee order without it | ORDER BY created_at DESC |
| Use LIMIT + OFFSET for pagination | Prevents returning millions of rows | LIMIT :limit OFFSET :offset |
| Whitelist dynamic ORDER BY columns | Prevents SQL injection in ORDER BY | allowedColumns.includes(sortBy) |
| Use indexes on JOIN / WHERE / ORDER BY | 10×–1000× faster queries | CREATE INDEX idx_user_id ON tasks(user_id) |
| Separate COUNT(*) query | Accurate total count for pagination | SELECT COUNT(*) as total FROM tasks |
| Log slow queries in production | Find performance killers | MySQL slow query log + Node.js request timing |
Which part would you like to go much deeper into next?
- Login + JWT authentication with MySQL
- Full task CRUD (create/read/update/delete + ownership)
- Add search, filtering, sorting, pagination all together
- Replace raw SQL with Prisma (most popular choice today)
- Replace raw SQL with Drizzle ORM (type-safe SQL)
- Add Docker + docker-compose with real MySQL container
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
