3 Most Frequently Asked SQL Join Interview Questions 

JOIN creates a logical bonding between the different tables and gets the required data from these tables efficiently.

Depending on what kind of logical bonding is needed between multiple tables, there are four basic types of SQL joins:

1. INNER JOIN
2. LEFT OUTER JOIN
3. RIGHT OUTER JOIN
4. FULL OUTER JOIN

This question helps the interviewer to understand if the candidate has a basic understanding of JOINs.

Well, this question usually has one or more of the below as follow-up questions.

Now, let us see how these JOINs work with the data using the examples below,

INNER JOIN

Logically, this returns the intersection of two datasets i.e. only the rows or records which are part of both the tables.

for example, I have dummy data about students.

And dummy data about orders.

Inner Join can be used as below,

SELECT student.Student_id, orders.order_id, orders.order_date
FROM student
INNER JOIN orders
ON student.Student_id = orders.Student_id
ORDER BY student.Student_id;

As you can see above, the query returned all rows from both tables where there is a matching Student_id value in both the student and orders tables.

The rows where Student_id is equal to 8000 and 9000 in the student table would be omitted since they do not exist in both tables. The row where the order_id is 5 from the orders table would be omitted since the Student_id of NULL does not exist in the student table.

Using Venn Diagrams it can be viewed as — 

OUTER JOIN

OUTER JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.

For example, Using the same student table as the previous example:

And the orders table with the following data:

Outer Join can be used as below,

SELECT Student.Student_id, orders.order_id, orders.order_date
FROM Student
OUTER JOIN orders
ON Student.Student_id = orders.Student_id
ORDER BY Student.Student_id;

There will be 7 records selected. These are the results that you should see:

This OUTER JOIN example would return all rows from the orders table and all rows from the student table.

A NULL value would be extended to those fields in the result set whenever the joined condition is not met. This means that if a Student_id value in the student table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. Also, if a Student_id value in the orders table, does not exist in the student table, all fields in the student table will display as NULL in the result set.

As you can see, the rows where the Student_id is 8000 and 9000 would be included but the order_id and order_date fields for those records contain a NULL value. The row where the order_id is 5 would be also included but the Student_id field for that record has a NULL value.

Using Venn Diagrams it can be viewed as —

RIGHT JOIN This Join returns all the rows from the right table in conjunction with the matching rows from the left table.

Now, let’s see how these JOINs work with the data using the examples below,

LEFT JOIN

This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null.

For example,Using the same student table as the previous example:

And the orders table with the following data:

Left Join can be used as below :

SELECT Student.Student_id, orders.order_id, orders.order_date
FROM Student
LEFT JOIN orders
ON Student.Student_id = orders.Student_id
ORDER BY Student.Student_id;

There will be 6 records selected. These are the results that you should see:

This LEFT JOIN example would return all rows from the student table and only those rows from the orders table where the joined fields are equal.

If a Student_id value in the student table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. As you can see, the rows where Student_id is 8000 and 9000 would be included with a LEFT JOIN but the order_id and order_date fields display NULL.

Using Venn Diagrams it can be viewed as —

RIGHT JOIN

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result set will contain null

For example, Using the same student table as the previous example:

And the orders table with the following data:

Right Join can be used as below,

SELECT Student.Student_id, orders.order_id, orders.order_date
FROM Student
RIGHT JOIN orders
ON Student.Student_id = orders.Student_id
ORDER BY Student.Student_id;

There will be 5 records selected. These are the results that you should see:

This RIGHT JOIN example would return all rows from the orders table and only those rows from the student table where the joined fields are equal.

If a Student_id value in the orders table does not exist in the student table, all fields in the student table will display as NULL in the result set. As you can see, the row where order_id is 5 would be included with a RIGHT JOIN but the Student_id field displays NULL.

Using Venn Diagrams it can be viewed as —