What are latest SQL Joins Interview Questions and answers?

In my previous article We have explained about the different SQL interview questions as well as BI interview questions. In this article I would like to give you the interview questions related to SQL Joins Interview questions and answers. You may called it as SQL Joins Interview Questions with its answers. Every SQL programmer should know about the joins in SQL. When its time to face the interview you may have to face SQL Joins Interview Questions. The SQL joins is most used concept in SQL.

• What You will find in this article ?

1. Most Common SQL Joins Interview questions with answers
2. SQL Joins Interview questions

Following are most common SQL Joins Interview Questions which will give you idea about SQL joins and will cover all kind of SQL Joins Interview Questions :

Question 1 : What is join? (Most asked SQL Joins Interview Questions )

Answer :

1.Joins are nothing but Combining the records from two or more tables. There are always two or more subsets and user needs to fetch the records from different subset to complete the requirement.

2.Joins are used to show the relationship between multiple tables and to fetch the specific subset of a data from multiple tables to fulfill the functional requirement.

3.Joins also selects the data from another table which is dependent on other table.

4.SQL joins are mostly used in reporting environment to select the data from multiple tables.

Example :

Select E.name,F.salary from Employee E,Employee_Salary F where E.Empno=F.Empno;

Question 2 : What is basic types of joins?(Most asked SQL Joins Interview Questions )

Answer :

There are two basic types of joins in SQL :

1.Joins using Operators -> Equi Join,Non Equi Join

2.Joins using Concept-> Inner Join,Outer Join,Cross Join,Self Join

Question 3: Explain Equi Join with example?(90% asked SQL Joins Interview Questions )

Answer :

1. Equi Join:

1.When two or more tables has been joined using equal to operator then this category is called as equi join.

2.Just we need to concentrate on condition is equal to(=) between the columns in the table.

3.Make sure that we are using where clause to apply the condition between two tables.

4.If the condition of join misses or there is not relation between the tables in the join then Equi join fails and the result will be the Cartesian product or cross join.

5.Syntax :

Select alias1.column_name1,alias1.column_name2,alias2.column_name1..

from table1 alias1,table2 alias2

where table1.column=table2.column;     —Equi join condition

6.Example :

Select  a.Employee_name,b.Department_name from Employee a,Employee b

where a.Department_ID=b.Department_ID;

The above query will fetch the Employee name from Employee table and Department name from department table.

Question 4: Explain Non Equi Join with example?

Answer :

Non Equi Join :
1.When two or more tables are joining without Equal to condition then that join is known as Non Equi join.

2.The use of non equi join is very rare in real life scenarios. You can join tables using any other attributes except equal to operator.

3.You can use any operator for non equi join that is <>,!=,<,>,Between.

4.Example :

Select b.Department_ID,b.Department_name from

Employee a,Department b where a.Department_id <> b.Department_ID;

5.What is Self join? Explain this with example.

Answer:

1.Self join is nothing but joining the table with itself.

2.User can join the multiple instances of same table together to complete the specific requirement.

3.Self-joins are used to compare values in a column with other values in the same column in the same table.

4.The Self join is used to obtain the running count and running totals.

5.Real Example:

The self join is used to find out the employee and his manager name in same employee table.

Query for the same is :

Select E.Employee_id,E.Name as ‘Employee Name’,F.Name as ‘Manager Name’ from Employee E,Employee F where E.Emp_jd=F.Mgr_id;

6.What is mean by Nested Join? 

Answer :

In nested joins, for each tuple in the outer join relation, the system scans the entire inner-join relation and appends any tuple that match the join-condition to the result set.

7.Explain Merge join ?

Answer:

1.Merge join If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus: It can consider the current group of tuple from the inner relation which consists of a set of contiguous tuple in the inner relation with the same value in the join attribute.

2.For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.

8.What is inner join in SQL?

Answer :

When 2 tables are connected such that it should retrieve only the matching records in both tables.Inner join select only the matching records between 2 tables.You can use Equal to(=) operator or Inner join keyword to apply inner join.This join is most widely used joins in real life applications,reporting,webapps,android apps.

Inner Joins is nothing but fetching the common records from two or more tables.

Syntax :

Type 1:

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col=t2.col;

Type 2:

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 inner join table2 t2

on t1.col=t2.col;

Example :

Select  a.Employee_name,b.Department_name

where a.Department_ID=b.Department_ID;

9.What is Outer join?

Answer :

When user fetches a data from left table and right table as well as its common records then this kind of join is known as Outer join.This is also one of the important join used in most of the real life scenarios.Outer join displays the common matching records between two tables and uncommon records from left or right table.The records not matching are considered as null.

10.What are different types of Outer joins?

Answer:

There are two types of Outer joins:

1.Left Outer Join

2.Right Outer Join

11.Explain Left Outer Join with Example?

Answer:

1.When user wants all the records from Left table (First table) and only equal or matching records from second table then Left outer join is useful.The unmatched records are considered as null records.

2.Syntax :

Type 1:Left Outer Join Syntax with + operator :

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col=t2.col(+);

Type 2:With Keyword :

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 Left Outer join table2 t2

on t1.col=t2.col;

3.Example :

Using Type 1:

Select a.Employee_name,b.Department_Name from

Employee a,Department b

where a.Department_ID=b.Department_ID(+);

Using Type 2:

Select a.Employee_name,b.Department_Name from

Employee a left outer join Department b

on.Department_ID=b.Department_ID;

12.Explain Right Outer Join with Example?

Answer:

1.When user wants all the records from Right table (Second table) and only equal or matching records from First or left table then Right outer join is useful.The unmatched records are considered as null records.

2.Syntax :

Type 1:Right Outer Join Syntax with +

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1,table2 t2

where t1.col(+)=t2.col;

Type 2:With Keyword

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 Right Outer join table2 t2

on t1.col=t2.col;

3. Examples :

Using Type 1:

Select b.Department_Name,a.Employee_name from

Employee a,Department b

where a.Department_ID(+)=b.Department_ID;

Using Type 2:

Select b.Department_Name,a.Employee_name from

Employee a right outer join Department b

on.Department_ID=b.Department_ID;

13.What is difference betweens joins and union?

Answer:

SQL Join :

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

SQL Union Operator :

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

Example :

SELECT * FROM EMPLOYEE1

UNION

SELECT * FROM EMPLOYEE2;

14.Is Self Join Inner Join or Outer Join?

Answer :

1.A self-join can be an inner join or an outer join or even a cross join.

2.A table is joined to itself based upon a column that have duplicate data in different rows.

15.Can you join table by itself? If Yes how? If no Why?

Answer:

1.Using Self-Joins,A table can be joined to itself in a self-join.

2.Use a self-join when you want to create a result set that joins records in a table with other records in the same table.

3.To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name.

16.Explain What is need of Join?

Answer :

1.Many times you are thinking “Why use SQL JOIN’s” as same task can be done using different queries.

2.In the database queries are executed one by one & result of successive query can be use for next query.

3.If we use the JOIN’s queries then instead of processing multiple queries SQL server process only single query which reduce the SQL server overhead.

4.The main advantage of SQL JOIN’s is the improved performance.

5.Also using multiple queries lead more data transfer from SQL server to application which reduces the performance.

17.What is full outer join? Explain with example?

Answer :

1.The Full Outer Join and Cartisian joins are less used joins in day to day applications,reporting,web applications.

2.These joins are less used because it deals with all the data from left table as well as right table. So if we want all the records from both left and right table we will directly use table no need to use the joins.

3.Syntax :

Select t1.col1,t2.col2….t ‘n’col ‘n.’.

from table1 t1 full join table2 t2

on t1.col=t2.col;

4.Example:

Select a.Department_ID,b.Employee_Name from

Employee b full outer join Department a

on a.Department_ID=b.Department_ID;

18.What is Cartesian join?

Answer:

This join is very less used join in day to day application.Developers have strict instructions that join should not be Cartesian product.Becase if we use this join then each and every record from first table will join to each and every record of second table.When we are not giving any joining condition then it displays Cartesian product.

19.What is Hash join?

Answer :

A hash join algorithm can only produce Equi-joins. The database system pre-forms access to the tables concerned by building hash tables on the join-attributes.

These are above most important SQL Joins Interview Questions. Hope you like this article on SQL Joins Interview Questions. If you have any suggestions regarding this article kindly comment in to comment section.