SQL Challenge: How to Get Employee Names and Department Names Using JOIN

Imagine your manager asks you to prepare a list of employees along with their department names.

You have two tables. Employee details are stored in one table, while department names are stored in another.

How would you write this query?

Let's look at the data.

1. Sample Tables

employees

emp_id	name	dept_id
101	Amit	10
102	Neha	20
103	Rahul	10

departments

dept_id	department
10	IT
20	HR

1. Expected Output

name	department
Amit	IT
Neha	HR
Rahul	IT

1. SQL Solution

SELECT
e.name,
d.department
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

1. Understanding the Query

Let's break it down.

• "employees e" gives the employees table an alias "e".
• "departments d" gives the departments table an alias "d".
• "INNER JOIN" combines rows that have matching values in both tables.
• "ON e.dept_id = d.dept_id" tells SQL how to match the records.

For example, Amit has department ID 10. SQL finds the matching department ID in the departments table and returns IT.

Similarly, Neha is matched with HR, and Rahul is matched with IT.

If you want to explore more examples of retrieving data from multiple tables, this guide on "displaying data from multiple tables in SQL" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/displaying-data-multiple-tables-sql-faq.html) explains the topic further.

1. Key Takeaway

JOIN is useful when related information is stored in different tables.

Here, "INNER JOIN" returns only employees whose department IDs have a matching record in the departments table.

Your Turn

What will happen if an employee has a department ID that does not exist in the departments table?

Will that employee appear in the result?

Share your answer in the comments!

sql #database #programming #beginners