What is the difference between INNER JOIN , OUTER JOIN , FULL OUTER JOIN ?

Difference Between INNER JOIN, OUTER JOIN, and FULL OUTER JOIN

In SQL, joins are used to combine rows from two or more tables based on a related column between them. The primary types of joins are INNER JOIN, OUTER JOIN (which includes LEFT OUTER JOIN and RIGHT OUTER JOIN), and FULL OUTER JOIN. Here is a detailed explanation of each:

INNER JOIN

• Definition: An INNER JOIN returns only the rows that have matching values in both tables.
• Behavior: It filters out rows from both tables that do not meet the join condition.
• Example: If you have two tables, employees and departments, an INNER JOIN will return only those employees who are assigned to a department.
• Syntax:
  sql Copy

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

• Result: Only rows with matching values in both tables are included in the result set.

OUTER JOIN

• Definition: An OUTER JOIN returns all rows from one table and the matching rows from another table. If there is no match, NULL values are included for columns from the non-matching table.
• Types:
  • LEFT OUTER JOIN: Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
  • RIGHT OUTER JOIN: Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
• Example: Using the same employees and departments tables, a LEFT OUTER JOIN will return all employees, including those who are not assigned to any department.
• Syntax:
  sql Copy

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

• Result: All rows from the left table and matching rows from the right table, with NULLs for non-matching rows.

FULL OUTER JOIN

• Definition: A FULL OUTER JOIN returns all rows from both tables, including matching and non-matching rows. If there is no match, NULL values are included for columns from the non-matching table.
• Behavior: It combines the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN.
• Example: Using the employees and departments tables, a FULL OUTER JOIN will return all employees and all departments, including employees not assigned to any department and departments with no employees.
• Syntax:
  sql Copy

  SELECT table1.column1, table2.column2
  FROM table1
  FULL OUTER JOIN table2
  ON table...