What is the difference between inner and outer joins?

Question

Accepted Answer

Inner join returns matching records, while outer join returns all records with matches where available.

Inner Join: Returns only the rows with matching values in both tables.
Example: SELECT * FROM A INNER JOIN B ON A.id = B.id; // Only rows with matching A.id and B.id.
Outer Join: Returns all rows from one table and matched rows from the other, filling with NULLs where no match exists.
Example: SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id; // All rows from A, matched rows from B, NULLs for non-matches.

Accepted Answer

Inner join returns the common and matching between the tables.snd outer join returns all the records from the database tables

Accepted Answer

Inner Join:

An inner join returns only the rows that have matching values in both tables based on a specified column. For example, if we have two tables employees and departments and we want to find out which employees belong to which departments based on the department ID, we can use an inner join.

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

In this example, we are selecting the name column from both tables and joining them based on the department_id and id columns, respectively. The result will be a table that shows the name of each employee and the name of their corresponding department.

Outer Join:

An outer join returns all the rows from one table and matching rows from another table based on a specified column. There are three types of outer join: left outer join, right outer join, and full outer join.

Left Outer Join:

A left outer join returns all the rows from the left table and matching rows from the right table based on a specified column. If there are no matching rows in the right table, the result will contain null values for the right table columns.

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

In this example, we are performing a left outer join and selecting the name column from both tables. The result will contain all the rows from the employees table and matching rows from the departments table, with null values for the departments columns where there are no matches.

Right Outer Join:

A right outer join returns all the rows from the right table and matching rows from the left table based on a specified column. If there are no matching rows in the left table, the result will contain null values for the left table columns.

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

In this example, we are performing a right outer join and selecting the name column from both tables. The result will contain all the rows from the departments table and matching rows from the employees table, with null values for the employees columns where there are no matches.

Full Outer Join:

A full outer join returns all the rows from both tables and matching rows based on a specified column. If there are no matching rows in either table, the result will contain null values for the corresponding columns.

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

In this example, we are performing a full outer join and selecting the name column from both tables. The result will contain all the rows from both tables and matching rows based on the department_id and id columns, respectively, with null values for the corresponding columns where there are no matches.

Asked in TCS and 15 others

What is the difference between inner and outer joins?

Interview Questions from Popular Companies