Top SQL Joins Interview Questions and Answers

SQL Joins are one of the most commonly used concepts while working with multiple tables. For this reason, interview questions based on SQL joins are frequently asked during interviews involving SQL.

In this article, we have compiled the top SQL joins interview questions (starting with the basic ones) that you should prepare for the interviews. So let’s begin.

SQL Joins Interview Questions

Ques.1. What are Joins in SQL?
Ans. Joins in SQL are the statements or clauses using which we can combine two or more tables, based on some common fields present among the tables.

A simple example can be combining the two tables – EmployeeDetails and EmployeeSalary. Now, we may look to join these two tables in order to get useful information like fetching the name of the employee getting the highest salary. Since the name is present in the EmployeeDetails table and salary is present in EmployeeSalary, so, we will join these two tables using joins.

SELECT * 
FROM EmployeeDetails INNERJOIN EmployeeSalary 
ON EmployeeDetails.EmpId = EmployeeSalary.EmpId; 

Ques.2. Explain the different types of Joins in SQL.
Ans. Some of the major joins in SQL are-

• Inner Join – Inner join is used to return the records which are having matching values in both tables.  
  
• Left Join – Left join is used to concatenate all the rows of the left table and the matching rows in the right table.
  
• Right Join-Right join is used to concatenate all the rows of the right table and the matching rows in the left table.
  
• Full Join-Full join is used to return all the records of both tables as long as there is a matching record in either table.
  
• Self Join-Self join is a join that is used to join a table to itself. In a self-join, a table is considered as if it were two tables.
  
• Cartesian Join-Cartesian join is used to return the number of rows in the first table multiplied by the number of rows in the second table. It is also referred to as cross-join.
  
  

Ques.3. What is the difference between Inner and Self Join?
Ans. A Self-join is a type of Inner join.
Inner join is used to return the records which are present in both tables. Whereas, in self-join, a table is joined to itself. 

Ques.4. What is the difference between Full Join and Cross Join?
Ans. A Full Outer Join is a combination of a Left Outer and Right Outer Join. It returns all rows in both tables that match the query’s WHERE clause, and in cases where the ON condition is not satisfied, it puts NULL values.
Whereas, a Cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. 

Ques.5. What is Natural Join?
Ans. Natural join is used to create an implicit join clause based on the value of common attributes in the two tables. Common attributes are the attributes that have the same name in both tables. Natural join does not need any comparison operator as in the case of equi join.

Example – In the below two tables the Employee and Department tables have a common field ‘EmpNo’. So, in the case of natural joins, we don’t need to mention the On clause over the ‘EmpNo’ field of both tables.

SELECT Employees.Name, Department.DName 
FROM Department
Natural JOIN Employees

Ques.6. What is an Equi Join?
Ans. An Equi Join is a type of join that combines tables based on matching values in the specified columns.

• The column names do not need to be the same.
• The resultant table can contain repeated columns.
• It is possible to perform an equi join on more than two tables. 

Syntax-

SELECT * 
FROM TableName1 
JOIN TableName2 
ON TableName1.ColumnName = TableName2.ColumnName;

Also check – SQL Query Interview Questions.

Ques.7. What is a non-equi join?
Ans. A non-equi join is a join in which join conditions use conditional operators instead of equals operators. Syntax-

SELECT * 
FROM TableName1 
JOIN TableName2 
ON TableName1.ColumnName [>,<,>=,<= ] TableName2.ColumnName;

Ques.8. What is the difference between cross-join and natural join?
Ans. A cross join produces a cross product or cartesian product of two tables whereas the natural join is based on all the columns having the same name and data types in both tables.


Ques.9. How are Joins different from the Union clause?
Ans. Some features of Joins are-

1. Join combines attributes of the rows present in the two tables that share some common fields or attributes.
2. Join is applicable when the two tables have at least one common attribute.
3. There are many types of joins like INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, etc.
4. The length of the resultant rows is more as compared to the length of rows of the tables involved.
   

Whereas in the case of Union-

1. Join combines rows of the tables that are present in the query.
2. Join is applicable when the number of columns present in the query is the same and the corresponding attributes have the same domain.
3. There are two types of union like UNION and UNION ALL.
4. The number of the resultant rows is more as compared to the number of rows present in each table involved in the query.

Ques.10. Write the SQL command to join 3 tables.
Ans. Consider the below 3 tables. Now in order to fetch EmpName, DeptName, Gender information of all the employees, we will join the 3 tables.

SELECT EmpName, DeptName, Gender
FROM Employees
JOIN Departement ON Employees.DeptId=Department.DeptId
JOIN Genders ON Employees.GenderId=Genders.GenderID;

This completes our article on top SQL joins interview questions. We hope, these questions will help you in your interviews.