Joining Tables in SQL

 Joining Tables

Joining tables is a powerful feature in SQL that allows you to combine rows from two or more tables based on a related column. Here’s a detailed breakdown of the various types of joins, including INNER JOIN, LEFT JOIN (OUTER JOIN), RIGHT JOIN, FULL JOIN, and SELF JOIN.

---

1. INNER JOIN

The INNER JOIN returns only the rows where there is a match in both tables. It excludes rows without matches in either table.

Syntax:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider two tables: employees and departments.

employees:

id	name	department_id
1	Alice	101
2	Bob	102
3	Carol	103

departments:

department_id	department_name
101	HR
102	IT

• To get a list of employees and their departments:

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

  Result:

  name  | department_name
  ------------------------
  Alice | HR
  Bob   | IT
  

Note: "Carol" is not included in the result because there is no matching department_id in the departments table.

2. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all rows from the left table (table1), and the matched rows from the right table (table2). If there is no match, the result will contain NULL for columns from the right table.

Syntax:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

• To get a list of all employees, even if they don't have a matching department:

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

  Result:

  
  name  | department_name
  ------------------------
  Alice | HR
  Bob   | IT
  Carol | NULL
  

Note: "Carol" is included with NULL for the department_name since there's no matching record in departments.

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN is the opposite of the LEFT JOIN. It returns all rows from the right table (table2), and the matched rows from the left table (table1). If there is no match, the result will contain NULL for columns from the left table.

Syntax:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

• To get a list of all departments, even if there are no employees in them:

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

  Result:

  
  name  | department_name
  ------------------------
  Alice | HR
  Bob   | IT
  NULL  | Sales
  

Note: If there was a department called "Sales" with no employees, it would be included with NULL in the name column.

4. FULL JOIN (FULL OUTER JOIN)

The FULL JOIN returns all rows when there is a match in either table. It returns NULL for unmatched rows from either the left or right table.

Syntax:

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Example:

• To get a list of all employees and all departments, even if there is no match between them:

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

  Result:

  name   | department_name
  -------------------------
  Alice  | HR
  Bob    | IT
  Carol  | NULL
  NULL   | Sales
  

Note: Both unmatched rows from employees (Carol) and departments (Sales) are included in the result.

5. SELF JOIN

A SELF JOIN is a join where a table is joined with itself. This is useful when you need to compare rows within the same table.

Syntax:

SELECT A.column1, B.column2, ...
FROM table_name A
INNER JOIN table_name B
ON A.column_name = B.column_name;

Example:

Consider an employees table with a manager_id:

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	David	2

• To get a list of employees and their managers:

  SELECT e1.name AS employee, e2.name AS manager
  FROM employees e1
  LEFT JOIN employees e2
  ON e1.manager_id = e2.id;
  

  Result:

  employee | manager
  ---------------------
  Alice    | NULL
  Bob      | Alice
  Carol    | Alice
  David    | Bob
  

Note: "Alice" has no manager, so NULL is shown for her manager.