What is the difference between "INNER JOIN" and "OUTER JOIN"?

In SQL, the terms "INNER JOIN" and "OUTER JOIN" refer to types of joins that determine how tables are combined based on their common fields. Both types of joins are fundamental in relational databases for querying data from two or more tables. Here’s a detailed explanation of the differences between them:

INNER JOIN

An "INNER JOIN" returns records that have matching values in both tables involved in the join. This type of join combines rows from two or more tables where the join condition is met, and it excludes rows where there is no match.

Example: Consider two tables, Employees and Departments.

• Employees has the columns EmployeeID, EmployeeName, and DeptID.
• Departments has the columns DeptID and DeptName.

If you want to list all employees and their corresponding department names, you might use an INNER JOIN like this:

SELECT Employees.EmployeeName, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

This query will only return the employees who have a valid department associated with them based on the DeptID.

OUTER JOIN

An "OUTER JOIN" returns all records from one table and the matched records from the second table. If there is no match, the result is NULL on the side of the table that does not have a match. There are three types of OUTER JOINs: LEFT, RIGHT, and FULL.

1. LEFT OUTER JOIN (or LEFT JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, the result will contain NULL on the part of the right table.

   Example:

   SELECT Employees.EmployeeName, Departments.DeptName
   FROM Employees
   LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;

   This query will return all employees including those without a department (these will show NULL for DeptName).

2. RIGHT OUTER JOIN (or RIGHT JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, the result will contain NULL on the part of the left table.

   Example:

   SELECT Employees.EmployeeName, Departments.DeptName
   FROM Employees
   RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;

   This query will return all departments, including those without any employees.

3. FULL OUTER JOIN (or FULL JOIN): Returns all records when there is a match in either left (table1) or right (table2) table records. If there is no match, the result will contain NULL on the side that does not have a match.

   Example:

   SELECT Employees.EmployeeName, Departments.DeptName
   FROM Employees
   FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;

   This query will return all employees and all departments, filling with NULLs where there is no match between them.

Conclusion

• Use INNER JOIN when you only want to retrieve records where there is at least one row in both tables that matches the join condition.
• Use OUTER JOIN when you also want to include rows that do not have matching rows in the other table. This is helpful for finding discrepancies or for inclusive reporting.

Choosing between INNER JOIN and OUTER JOIN depends on the specific requirements of your query and what you need to achieve with your data retrieval.