Database Engineering
  • Need of Database
  • Database and Database Management System
  • What is Relational Database Model ?
  • Installing Postgresql
    • Installing PostgreSQL on Linux
    • Installing PostgreSQL on Windows
    • Installing PostgreSQL on Mac
    • Postgres in Docker
  • What happens during the initial installation ?
    • Roles, Users & Groups
    • More examples on Roles
  • Sample Databases
    • DVD Rental Database
  • Querying Data
    • SELECT Query
    • Column Aliases
    • Order By
    • SELECT DISTINCT
    • DB-TASK-001
  • Filtering Data
    • WHERE ?
    • AND Operator
    • OR Operator
    • LIMIT
    • FETCH
    • IN
    • BETWEEN
    • LIKE
    • SIMILAR TO
    • IS NULL
    • ESCAPE
    • DB-TASK-002
  • Entity Relationship Diagram
    • What is an ER Diagram ?
    • Entity
    • Identifiers
    • Relationship
    • Attributes
    • Cardinality
    • Entity Relationship Diagram Tutorial
    • DB-TASK-003
  • Joins
    • Joins
    • Joins With Employee and Departments
  • Joins on E-Commerce
Powered by GitBook
On this page
  1. Joins

Joins With Employee and Departments

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,

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.

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

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.

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

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

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.

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).

PreviousJoinsNextJoins on E-Commerce

Last updated 5 months ago