Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Joins interview questions and answers

In this article I will share some frequently asked table join interview questions and answers in SQL server.

1. What is table join?

A SQL join clause combines records from two or more tables in a relational database.

Basically there are three types of table joins

1. Inner join
2. Outer join
3. Cross join

2. What are Different Types of table joins?

Following different types of table joins used in sql server
a. Inner join
b. Outer join
Outer join categorized in three types
1. Left outer join
2. Right outer join
3. Full outer join
c. Cross join
d. Self join

3. What is inner join?

This join returns common or matching records/rows from both the database tables (left table and right table).This s the most used join in the SQL. In the join condition, you can also use other operators like <,>,<>.

Example:

SELECT

ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName

AS [Product Category],ImagePath

AS [Image]
 
FROM Products

P
INNER JOIN Categories C ON

P.CategoryID=C.CategoryID

More info.. http://www.codechef4u.com/post/2015/04/10/inner-join

4. What is outer join?

Outer join return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

 All rows from both tables are returned in a full outer join

5. What is cross join?

A cross joins that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.

More info.. http://www.codechef4u.com/post/2015/04/10/cross-join

6. What is self join?

A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table.

More info.. http://www.codechef4u.com/post/2015/04/10/self-join

7. Explain left outer join and right outer join?

 The LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table (Categories). The result is NULL in the right side when there is no match.

More info.. http://www.codechef4u.com/post/2015/04/10/left-outer-join-or-left-join

8. What is full outer join?

Full outer join returns all the rows from both tables whether it has been matched or not.The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SELECT ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
FULL OUTER JOIN Categories C ON P.CategoryID=C.CategoryID

More info.. http://www.codechef4u.com/post/2015/04/10/full-outer-join

9. Equi join and non equi join is possible with sql server?

Yes that is supported in sql server, check more info http://www.codechef4u.com/post/2015/04/10/equi-join-and-natural-join

10. Inner vs outer joins?

Inner join:

This join returns common or matching records/rows from both the database tables (left table and right table).

Inner joins return rows only when there is at least one row from both tables that matches the join condition.

Inner joins eliminate the rows that do not match with a row from the other table

Outer join:

Outer join return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

 All rows from both tables are returned in a full outer join.

11. Natural join is possible with sql server?

Not supported by sql server,check more info.. http://www.codechef4u.com/post/2015/04/10/equi-join-and-natural-join


12. How to use JOIN in SQL with one-to-many relationship and one to one relationship?

In One-to-many relationship you can use foreign key and one to one relationship is quite simple ,here you can use two table’s primary key for join.

For more details use following link that explained question with good examples…

https://www.deskbright.com/sql/sql-joins-interview-questions/

Invalid entry,please enter valid data.

Loading