Example: JOIN Queries

 

Example: JOIN Queries Using Two Tables

We will use a Company Database with two tables:

• employees

• departments

---

1. Create Tables

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

---

2. Insert Sample Data

INSERT INTO departments VALUES
(10, 'Human Resources'),
(20, 'Finance'),
(30, 'Engineering'),
(40, 'Marketing');

INSERT INTO employees VALUES
(1, 'Alice', 50000, 10),
(2, 'Bob', 60000, 20),
(3, 'Charlie', 70000, 30),
(4, 'David', 55000, 30),
(5, 'Eva', 65000, NULL);   -- employee without department

---

3. JOIN Queries With Outputs

---

3.1 INNER JOIN: Employees With a Department

SELECT emp_id, emp_name, salary, dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id;

Output

emp_id	emp_name	salary	dept_name
1	Alice	50000	Human Resources
2	Bob	60000	Finance
3	Charlie	70000	Engineering
4	David	55000	Engineering

(Eva is excluded because she has no dept_id)

---

3.2 LEFT JOIN: All Employees + Department (if any)

SELECT emp_id, emp_name, salary, dept_name
FROM employees
LEFT JOIN departments
ON employees.dept_id = departments.dept_id;

Output

emp_id	emp_name	salary	dept_name
1	Alice	50000	Human Resources
2	Bob	60000	Finance
3	Charlie	70000	Engineering
4	David	55000	Engineering
5	Eva	65000	NULL

(Eva appears with NULL since she has no department)

---

3.3 RIGHT JOIN: All Departments + Employees (if any)

(Useful when some departments have no employees)

SELECT emp_id, emp_name, dept_name
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.dept_id;

Output

emp_id	emp_name	dept_name
1	Alice	Human Resources
2	Bob	Finance
3	Charlie	Engineering
4	David	Engineering
NULL	NULL	Marketing

(Marketing dept has no employees)

---

3.4 FULL OUTER JOIN (MySQL simulation)

SELECT emp_id, emp_name, dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id

UNION

SELECT emp_id, emp_name, dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;

Output

emp_id	emp_name	dept_name
1	Alice	Human Resources
2	Bob	Finance
3	Charlie	Engineering
4	David	Engineering
5	Eva	NULL
NULL	NULL	Marketing

---

4. Additional Useful JOIN Queries

---

4.1 Employees with salary > 55000 and their department

SELECT emp_name, salary, dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id
WHERE salary > 55000;

---

4.2 Count of employees in each department

SELECT departments.dept_name, COUNT(employees.emp_id) AS total_employees
FROM departments
LEFT JOIN employees
ON departments.dept_id = employees.dept_id
GROUP BY departments.dept_name;

---

4.3 Departments having no employees

SELECT dept_name
FROM departments
LEFT JOIN employees
ON departments.dept_id = employees.dept_id
WHERE employees.emp_id IS NULL;