Explain inner and outer join

Question

Accepted Answer

Inner join returns only the matching records from both tables, while outer join returns all records from both tables.

Inner join combines rows from two tables based on a related column.
Outer join combines rows from two tables and includes unmatched rows from one or both tables.
Inner join is used to retrieve data that exists in both tables.
Outer join is used to retrieve data that may or may not exist in both tables.
Examples: Inner join - SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
Outer join - SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

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.

Explain inner and outer join

Top Razorpay Associate Analytics interview questions & answers

Popular interview questions of Associate Analytics

Top HR questions asked in Razorpay Associate Analytics