# Joins With Employee and Departments

```sql
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);

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

Insert data into the tables, 

```sql
INSERT INTO departments (dept_name) VALUES 
('HR'), 
('Finance'), 
('IT');

INSERT INTO employees (emp_name, dept_id, salary) VALUES 
('Alice', 1, 50000), 
('Bob', 2, 60000), 
('Charlie', 3, 70000), 
('David', NULL, 55000);

```

#### **INNER JOIN**

**Definition:** Combines rows from two tables when there is a matching value in the specified columns.

* **Use Case:** When you want only the records that have corresponding entries in both tables.
* **Real-World Example:** Show employees and their respective departments.

```sql
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d 
ON e.dept_id = d.dept_id;
```

<figure><img src="/files/8JeXp38CxJdUVYaColxL" alt=""><figcaption></figcaption></figure>

**Explanation:**\
The `INNER JOIN` only includes employees who belong to a department. Employees without a department will not appear in the result.

#### **LEFT JOIN (or LEFT OUTER JOIN)**

**Definition:** Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for columns of the right table.

* **Use Case:** When you want to see all entries in one table (left) and their matches, if they exist, in the other table.
* **Real-World Example:** List all employees, including those who don’t belong to any department.

```sql
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d 
ON e.dept_id = d.dept_id;
```

<figure><img src="/files/MGY8GDQTfkZVJ7JuHtMT" alt=""><figcaption></figcaption></figure>

#### **RIGHT JOIN (or RIGHT OUTER JOIN)**

**Definition:** Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for columns of the left table.

* **Use Case:** When you want to ensure that all entries from the right table are included, even if there’s no match in the left table.
* **Real-World Example:** List all departments, even those with no employees

```sql
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d 
ON e.dept_id = d.dept_id;
```

All departments appear in the result. If a department has no employees, the employee column will show `NULL`.

#### **FULL OUTER JOIN**

**Definition:** Combines the results of both `LEFT JOIN` and `RIGHT JOIN`. Returns all rows from both tables, with NULLs for non-matching rows.

* **Use Case:** When you need to see everything, even if there are no matches in either table.
* **Real-World Example:** List all employees and departments, including unmatched records from both.

```sql
SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d 
ON e.dept_id = d.dept_id;

```

**Explanation:**\
The result will include

1. Employees with departments.
2. Employees without departments (`dept_name = NULL`).
3. Departments with no employees (`emp_name = NULL`).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://courses.parottasalna.com/database-engineering/joins/joins-with-employee-and-departments.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.