Sql Server join

What is a join and explain different types of joins.

Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is a self join in SQL Server?

Two instances of the same table will be joined in the query. 

SQL Server join -

Explain Nested Join, Hash Join and Merge Join in SQL query plan.

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

Merge join
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 tuples from the inner relation which consists of a set of contiguous tuples in the inner relation with the same value in the join attribute. 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. 

Hash join
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. 

SQL Server Joins -

Define SQL Server Join.

Answer
A SQL server Join is helps to query data from two or more tables between columns of these tables. A simple JOIN returns data for at least one match between the tables. The columns need to be similar. Usually primary key one table and foreign key of another is used.

Syntax:

Table1_name JOIN table2_name ON table1_name.column1_name= table2_name.column2_name.

What is inner join? Explain with an example

Answer
INNER JOIN: Inner join returns rows when there is at least one match in both tables.

Syntax:

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

Example: To display records of an employee who got an appraisal.

SELECT employee.firstname, appraisal.amount FROM employee INNER JOIN appraisal ON employee.id = appraisal.employee.id;

What is outer join? Explain Left outer join, Right outer join and Full outer join.

Answer
OUTER JOIN: In An outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.

LEFT OUTER JOIN: A left outer join or a left join returns results from the table mentioned on the left of the join irrespective of whether it finds matches or not. If the ON clause matches 0 records from table on the right, it will still return a row in the result—but with NULL in each column.

Example: To display employees irrespective of whether they have got bonus. 
Select * From employee LEFT OUTER JOIN bonus ON employee.bonusID=bonus.bonusID

RIGHT OUTER JOIN: A right outer join or a right join returns results from the table mentioned on the right of the join irrespective of whether it finds matches or not. If the ON clause matches 0 records from table on the left, it will still return a row in the result—but with NULL in each column.

Example: To display Bonus irrespective of whether they are an employee or not.
Select * From employee RIGHT OUTER JOIN bonus ON employee.bonusID=bonus.bonusID

FULL OUTER JOIN: A full outer join will combine results of both left and right outer join. Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.

Example: To display employees who have a bonus and to display bonus even if he is not an employee.
Select * From employee FULL OUTER JOIN bonus ON employee.bonusID=bonus.bonusID