3 Most Frequently Asked SQL Join Interview Questions 

aparna Last Updated : 22 Jun, 2022
6 min read

This article was published as a part of the Data Science Blogathon.

Introduction on SQL Join

The data required for a data-analysis task usually comes from multiple sources. SQL JOINs are the tools to bring this data together.

SQL JOIN is the best tool to combine data from multiple resources!

In this article, I am discussing the most commonly and frequently asked SQL interview questions around SQL JOINs. I am also sharing examples to demo the concepts.

Starting with the most basic and easiest question to answer.

What is SQL JOIN and what are different SQL JOINs?

In SQL, JOIN clause is used to combine rows from two or more tables, based on a related column between them.

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.

What is the Difference between INNER JOIN and OUTER JOIN?

The primary difference between the two is, while joining 2 tables,
INNER JOIN extracts only the rows which are common in both tables and OUTER JOIN extracts also the rows which are not common in both tables in addition to common rows between the both.

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.

Dummy Students Data
Dummy Students Data | Image by Author

And dummy data about orders.

Dummy Orders Data
Dummy Orders Data | Image by Author

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;
Output of Inner JOIN
Output of Inner JOIN | Image by Author

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 — 

Venn Diagrams
Image by Author

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:

OUTER JOIN

And the orders table with the following data:

OUTER JOIN Image 2

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:

7 records

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 —

 

Venn Diagrams
Image By Author

What is the Difference between LEFT JOIN and RIGHT JOIN?

The primary difference between the two is while joining 2 tables,
LEFT JOIN This join returns all the rows from the left table in conjunction with the matching rows from the right table. and

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:

LEFT JOIN

And the orders table with the following data:

LEFT JOIN

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:

LEFT JOIN IMAGE 3

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 —

 

Venn Diagrams
Image By Author

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:

RIGHT JOIN

And the orders table with the following data:

RIGHT JOIN Image 2

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:

RIGHT JOIN Image 3

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 —

 

Venn Diagrams| Image 3
Image By Author

 

Conclusion on SQL Join

I hope you find this article interesting and useful. It is always good to know different types of SQL JOINs although most of the time you really use only INNER JOIN and LEFT JOIN.

Let’s quickly recap on the types of join we discussed.

INNER JOIN: Combines rows from two tables whenever the join condition is met.

LEFT JOIN: Same as an inner join, except rows from the first table is added to the join table, regardless of the evaluation of the join condition.

RIGHT JOIN: Same as an inner join, except rows from the second table is added to the join table, regardless of the evaluation of the join condition.

FULL OUTER JOIN: A combination of left join and right join.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

My name is Aparna Gurav, ServiceNow Developer Engineer/Platform Engineer/ at Sportradar Skilled in JavaScript/ SQL /SAP/ ITSM, CMDB,/Advanced Excel/Microsoft Power BI/System Admin Currently staying in Munich Germany. I like to learn New Things, Reading books and write blogs and Articles.

Responses From Readers

Clear

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details