Explain the differences between inner join, left join, right join, and full outer join in SQL.? — Software Engineering Interview Question #27

When working with databases, it is common to join tables together to retrieve data from multiple tables at once. SQL provides four types of joins: inner join, left join, right join, and full outer join.

**Inner Join:**

An inner join selects records that have matching values in both tables. It returns only the rows that have common values in the specified columns of both tables. In other words, it returns the intersection of two tables. The syntax of inner join is as follows:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Here is an example of inner join. Suppose we have two tables: Customers and Orders. The Customers table contains customer id, name, email, and country columns. The Orders table contains order id, customer id, order date, and amount columns. We want to select all the customers who have placed an order. We can use inner join as follows:

SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id;

This will return a table of all the customers who have placed an order, with all their information and the information about their orders that matches their customer_id.

**Left Join:**

A left join selects all records from the left table and the matched records from the right table. It returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, it returns NULL values. In other words, left join returns all the records from the left table and the common records from the right table. The syntax of left join is as follows:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Here is an example of left join. Suppose we have the same Customers and Orders tables as before. If we want to select all the customers and also their orders if they have any, we can use left join as follows:

SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

This will return a table of all the customers, with their orders if they have any, and NULL values if they don’t have any orders.

**Right Join:**

A right join selects all records from the right table and the matched records from the left table. It returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, it returns NULL values. In other words, right join returns all the records from the right table and the common records from the left table. The syntax of right join is as follows:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Here is an example of right join. Suppose we have the same Customers and Orders tables as before. If we want to select all the orders and also the customer information if it exists, we can use right join as follows:

SELECT *
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

This will return a table of all the orders, with the customer information if it exists, and NULL values if there is no customer information for that order.

**Full Outer Join:**

A full outer join selects all records from both tables. It returns all the rows from both tables and NULL values where there is no match. In other words, full outer join returns the union of left join and right join. The syntax of full outer join is as follows:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Here is an example of full outer join. Suppose we have the same Customers and Orders tables as before. If we want to select all the customers and all the orders, we can use full outer join as follows:

SELECT *
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer_id;

This will return a table of all the customers and all the orders, with NULL values where there is no match.