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/

Difference between delete and truncate

In SQL Server there are ways you can delete rows from a table.   You can use the TRUNCATE and DELETE command.

In this article I will share key difference between DML delete statement and DDL Truncate command.

Delete:

The DELETE statement is used to delete records in a table, using where clause you can delete specific rows.

Truncate:

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions

Difference:

Difference

Delete

Truncate

Condition

It deletes specified data using where condition.

Removes all records, Cannot use Where Condition.

Rollback

Rollback is possible.

Rollback is not possible.

DDL or DML

DELETE is a DML Command.

TRUNCATE is a DDL command.

Lock

DELETE statement is executed using a row lock; each row in the table is locked for deletion.

 

TRUNCATE TABLE always locks the table and page but not each row.

Performance

Slower than truncate because, it keeps logs.

Faster in performance wise, because it doesn't keep any logs.

Trigger

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

 

Delete activates a trigger because the operation is logged individually.

 

Difference between Primary Key and Unique Key

Primary key and unique key seems identical, but reality is that both are different in features and behaviours.

In this article I will share key differences between unique key and primary key.

Primary Key:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

Unique key:

Unique key enforces uniqueness or prevent duplicate data on columns.
Single null value is allowed with unique key

Differences :

Difference

Primary Key

Unique Key

Limit

We can have only one Primary key in a table

We can have more than one unique key in a table.

Null values

Primary Key can't accept null values.  

Unique key can accept only one null value.

Index

By default, Primary key is clustered index.

Unique key is a unique non-clustered index.

Columns

Only single unique column.

Combination of column values allowed.

Foreign key

Primary key can be made foreign key into self and another table.

With sql server, Unique key can be made foreign key into another table.

Auto Increment

Primary key supports Auto Increment value.

Unique Constraint doesn't support Auto Increment value.