SQL joins: INNER JOIN, LEFT JOIN, RIGHT JOIN,

 SQL joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN

Here's a detailed explanation of the different types of SQL joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Sample Tables

Table: employees

-- Create the 'employees' table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

-- Insert sample data into 'employees'
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', NULL),
(4, 'David', 3);

Table: departments

-- Create the 'departments' table
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- Insert sample data into 'departments'
INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Sales'),
(4, 'Marketing');

1. INNER JOIN

Purpose: To retrieve only the rows with matching values in both tables. Step-by-Step:

1. Identify Common Column: The common column is department_id in the employees table and id in the departments table.
2. Match Rows:

• Alice (department_id = 1) matches with HR (id = 1).
• Bob (department_id = 2) matches with IT (id = 2).
• David (department_id = 3) matches with Sales (id = 3).
• Charlie has a NULL in department_id, so it does not match any department.

3. Select Columns: From the matched rows, select name from employees and department_name from departments.

Query:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Result:

2. LEFT JOIN

Purpose: To retrieve all rows from the left table and the matched rows from the right table. Step-by-Step:

1. Identify Common Column: Same as before. 2. Match Rows:

• Matches are the same as in the INNER JOIN.
  
• Addiionally, include rows from employees that do not have a match in departments.

3. Include All Employees: Charlie will be included with NULL for department_name.

Query:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

Result:

3. RIGHT JOIN

Purpose: To retrieve all rows from the right table and the matched rows from the left table.

Step-by-Step:

1. Identify Common Column: Same as before. 2. Match Rows:

• Matches are the same as in the INNER JOIN.
• Additionally, include rows from departments that do not have a match in employees.

3. Include All Departments: Marketing will be included with NULL for employees.name.

Query:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

Result:

4. FULL JOIN

Purpose: To retrieve all rows when there is a match in either left or right table. Step-by-Step:

1. Identify Common Column: Same as before. 2. Match Rows:

• Include all matches from both tables.
  
• Include non-matching rows from employees (like Charlie) and departments (like Marketing).

3. Include All Rows: Both matched and unmatched rows will be included.

Query:

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id;

* Alternative for MySQL If you are using MySQL, you can achieve a similar result using the UNION of LEFT JOIN and RIGHT JOIN:

Query:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id

UNION

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Result:

 

Summary of Results

• INNER JOIN: Only matching employees with departments.
  
• LEFT JOIN: All employees, including those without a department.
  
• RIGHT JOIN: All departments, including those without employees.
  
• FULL JOIN: All employees and all departments, regardless of matches.

This detailed breakdown should help you understand how each type of join works step by step!