🧠 Node.js – MySQL: Advanced Queries – JOINs & Table Dropping in Practice

🧲 Introduction – Move Beyond Basics with Powerful MySQL + Node.js Queries

Once you’ve mastered basic CRUD operations, it’s time to leverage advanced SQL queries in Node.js applications. This includes using JOINs to combine data across multiple tables and safely dropping tables when needed.

🎯 In this guide, you’ll learn:

• How to use INNER JOIN, LEFT JOIN, and RIGHT JOIN in Node.js
• How to combine rows from related tables using foreign keys
• How to safely drop (delete) tables using SQL via Node.js

---

📘 Topics Covered

🔹 Topic	📖 Description
🔗 Node.js – MySQL Join Queries	Combine multiple tables using INNER, LEFT, and RIGHT JOINs
🗑️ Node.js – MySQL Drop Table	Permanently remove a table from the database

---

🔗 Node.js – MySQL Join Queries

🔹 Scenario: Employees & Departments

Assume you have two tables:

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

✅ Let’s use JOIN to connect employee names with their departments.

---

🔹 INNER JOIN – Get Only Matching Records

const sql = `
  SELECT employees.name AS employee, departments.name AS department
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.id
`;

db.query(sql, (err, results) => {
  if (err) throw err;
  console.log(results);
});

🧪 Output:

[
  { employee: "Alice", department: "Sales" },
  { employee: "Bob", department: "IT" }
]

✅ Only returns employees assigned to a valid department.

---

🔹 LEFT JOIN – Include All Employees, Even Without Department

const sql = `
  SELECT employees.name AS employee, departments.name AS department
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.id
`;

db.query(sql, (err, results) => {
  if (err) throw err;
  console.log(results);
});

🧪 Output:

[
  { employee: "Alice", department: "Sales" },
  { employee: "Charlie", department: null }
]

✅ Useful for identifying unassigned or incomplete records.

---

🔹 RIGHT JOIN – Include All Departments

const sql = `
  SELECT employees.name AS employee, departments.name AS department
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.id
`;

db.query(sql, (err, results) => {
  if (err) throw err;
  console.log(results);
});

🧪 Output:

[
  { employee: "Bob", department: "IT" },
  { employee: null, department: "HR" }
]

✅ Useful for seeing which departments have no employees.

---

🗑️ Node.js – MySQL Drop Table

🔹 Drop a Single Table

db.query("DROP TABLE employees", (err, result) => {
  if (err) throw err;
  console.log("Table 'employees' dropped.");
});

🧪 Output:

Table 'employees' dropped.

✅ Always ensure backups before executing destructive operations.

---

🔹 Drop Table If Exists

db.query("DROP TABLE IF EXISTS departments", (err, result) => {
  if (err) throw err;
  console.log("Table 'departments' dropped (if existed).");
});

🧪 Output:

Table 'departments' dropped (if existed).

✅ This avoids errors if the table doesn’t exist.

---

📌 Summary – Recap & Next Steps

Advanced SQL queries like JOINs are critical for connecting and analyzing data across tables, while DROP TABLE enables cleanup and schema management. Node.js makes it easy to execute and manage such operations programmatically.

🔍 Key Takeaways:

• INNER JOIN fetches matching records across tables
• LEFT JOIN includes unmatched records from the left table
• RIGHT JOIN includes unmatched records from the right table
• DROP TABLE permanently deletes a table structure

⚙️ Real-World Uses:

• Multi-table reporting in admin dashboards
• Cleaning up test or outdated database schemas
• Creating complex relationships in e-commerce systems (e.g., products & categories)

---

❓ Frequently Asked Questions

❓ What is the difference between INNER and LEFT JOIN?
✅ INNER JOIN returns matching rows only. LEFT JOIN returns all rows from the left table—even if there’s no match.

---

❓ Is it safe to use DROP TABLE in production?
✅ Use with caution. Always back up data and use DROP TABLE IF EXISTS to avoid fatal errors.

---

❓ Can I join more than two tables in a single query?
✅ Yes. You can chain multiple JOINs to query across three or more related tables.

---

❓ What happens if I drop a table with foreign keys?
✅ MySQL will throw an error unless CASCADE is enabled or keys are removed first.

---

Share Now :