SQL Interview Questions

The primary difference between INNER JOIN and OUTER JOIN lies in how they handle the inclusion of rows that do not have matching values in both tables.

INNER JOIN

Description:

• An INNER JOIN returns only the rows where there is a match in both tables.
• If a row in one table does not have a corresponding row in the other table, it is excluded from the result set.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Result:

• Only employees who are assigned to a department will be included in the result. If an employee is not assigned to any department (i.e., department_id is NULL or there is no corresponding entry in the departments table), that employee will not appear in the result set.

OUTER JOIN

Description:

• An OUTER JOIN returns all rows from one table and the matched rows from the other table. If there is no match, NULL values are returned for columns from the table that lacks a match.
• There are three types of OUTER JOINs: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN

Description:

• Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Result:

• All employees will be included in the result set. If an employee is not assigned to any department, the department_name will be NULL.

RIGHT OUTER JOIN

Description:

• Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Result:

• All departments will be included in the result set. If a department does not have any employees assigned, the name will be NULL.

FULL OUTER JOIN

Description:

• Returns all rows when there is a match in either left or right table. Rows without a match in either table will contain NULL for the columns of the table that lacks a match.

Syntax:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Result:

• All employees and all departments will be included in the result set. If an employee is not assigned to any department, the department_name will be NULL. If a department does not have any employees, the name will be NULL.

Key Differences

Feature	INNER JOIN	OUTER JOIN
Return Rows	Only matching rows from both tables	All rows from one or both tables
Matched Rows	Required	Optional (NULLs for non-matching rows)
Variants	None (only INNER JOIN)	LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

In summary, INNER JOIN is used when you want to return only the rows with matching data in both tables, while OUTER JOIN (in its various forms) is used to return all rows from one or both tables, with NULLs in place where there is no match.