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.
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.
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.
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?
- Join combines attributes of the rows present in the two tables that share some common fields or attributes.
- Join is applicable when the two tables have at least one common attribute.
- There are many types of joins like INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, etc.
- The length of the resultant rows is more as compared to the length of rows of the tables involved.
- Join combines rows of the tables that are present in the query.
- Join is applicable when the number of columns present in the query is the same and the corresponding attributes have the same domain.
- There are two types of a union like UNION and UNION ALL.
- 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
Table – Gender
SELECT EmpName, DeptName, Gender FROM Employees JOIN Departement ON Employees.DeptId=Department.DeptId JOIN Genders ON Employees.GenderId=Genders.GenderID;