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 a combining below 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 EmployeeDetails table and salary is present in EmployeeSalary, so, we will join these two tables using joins.

EmpIdNameManagerId
121John321
321Walter986
421Nick589

EmployeeDetails

EmpIdProjectSalary
121P18000
321P29000
421P39500

EmployeeSalary

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 the 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 the 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 the 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 the 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 case of natural joins, we don’t need to mention the On clause over the ‘EmpNo’ field of both the tables.

EmpIdNameCity
568KDSeattle
897PKAustin
215PPMiami

Employees

DeptIdDNameEmpId
5466HR897
5219IT215
8795HK568

Department

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;



Ques.7. What is the difference between cross join and natural join?
Ans. The cross join produces the 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 the tables.


Ques.8. How are Joins different from Union clause?
Ans. JOIN-

  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.

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 a 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.9. 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.

Table – Employees

EmpNoEmpNameDeptIdGenderId
1886John Snow1011
1889Amara Giselle1022
1890Richie Tung1021
DeptIdDeptName
101HR
102IT

Table – Department

GenderIdGender
01Male
02Female

Table – Gender

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

Leave a Comment