D. SQL Joins

1. What is a Join? List its different types.

A JOIN clause is used to combine rows from two or more tables. The four types of JOIN are INNER, LEFT, RIGHT and FULL. The join command in sql is used to join related data stored in one or more tables. You can join one table to another, to fetch combined results.

A. INNER JOIN

INNER JOIN selects records that have matching values in both tables.

1SELECT name
2FROM customers
3INNER JOIN orders
4ON customers.customer_id = orders.customer_id;

B. LEFT JOIN

LEFT JOIN selects records from the left table that match records in the right table. In the below example the left table is customers.

1SELECT name
2FROM customers
3LEFT JOIN orders
4ON customers.customer_id = orders.customer_id;

C. RIGHT JOIN

RIGHT JOIN selects records from the right table that match records in the left table. In the below example the right table is orders.

1SELECT name
2FROM customers
3RIGHT JOIN orders
4ON customers.customer_id = orders.customer_id;

D. FULL JOIN

FULL JOIN selects records that have a match in the left or right table. Think of it as the OR join.

1SELECT name
2FROM customers
3FULL OUTER JOIN orders
4ON customers.customer_id = orders.customer_id;

2. What is the difference between a LEFT and RIGHT JOIN?

A LEFT JOIN returns all of the rows from the left table and fills the missing values from the right table with NULL values.

A RIGHT JOIN is the inverse of a left join in that it returns all of the rows from the right table while filling the missing values from the left table with NULL values.

3. What is a Self Join?

While most JOINs connect two or more tables to present their data together, a self-join links a table to itself based on its own column(s).

A self-join uses the INNER JOIN or LEFT JOIN clause, and a table alias is used to assign different names to the table within the query.

Let's look at an actual interview question to see this in practice.

Question Level: Hard

Amazon is comparing the monthly wages of their employees in each department to those of their managers and co-workers.

Create a table that compares an employee's salary to that of their manager and to the average salary of their department.

Note: Since managers are their own manager they should be removed from the employees averages as not to skew the results.

Before we get to the solution, consider the following scenario: we have need to create two tables from the table employee to separate the employees from their managers. In our solution we will refer to the employee table as a and the manager table as b.

1SELECT a.department,
2       a.id AS employee_id,
3       a.salary AS employee_salary,
4       b.salary AS manager_salary,
5       ROUND(AVG(a.salary) OVER(PARTITION BY a.department)) AS avg_employee_salary
6FROM employee a
7JOIN employee b 
8ON a.manager_id = b.id
9AND a.id <> b.id
10GROUP BY 1, 2, 3, 4
11ORDER BY 1, 3 DESC;

Result

So by using a self join we can connect the table employee to itself and using a Window Function we can get the AVG salary of employees.

4. What is a Cross-Join?

Cross join also known as a Cartesian product is a table in which each row of the left table joins with each row of the right table, resulting in all possible ordered pairs.

Looking at the two tables below, we can cross join them to produce the table on the right.