Full Outer Join

This logical operator returns all the records from any one of the table and non-matching records from the another tables which will be based on the joining condition.

Or, we can say

Non matching records from one table + matching records of both table + non matching records from another table

 

Example

 



Lets have data as below :-

 

 

 

Now use Full Outer Join (we can use as Full Join also) and have a look at returned result set as below :-

 

 

 

Right Outer Join

This logical operator returns all the records from right hand side table and similar records from left hand side table.

Example

Lets have data as below :-

Now use Right Outer Join (we can use as Right Join also) and have a look at returned result set as below :-

Facebook page


 

Left Outer Join

This logical operator returns all the records from left hand side table and similar records from right hand side table.

Example: Lets have data as below :-

Now use Left Outer Join (we can use as Left Join also) and have a look at returned result set as below :-

 

Outer Join

In the case of Outer Join, SQL Server returns all the data from at least one table and matching records from the another table based on the column being used in Join condition.

We have below type of Outer Join :-

• Left Outer Join
• Right Outer Join
• Full Outer Join

 

 

 

 

Inner Join

Inner join logical operator returns those records from more than one tables which have the exact value in the column which is being used in joining condition.
Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.

Example:-

If we have duplicate records then inner join behaves like cross join for those duplicate records.

Example:-

 

Join

Join operator allow us to join two or more than two tables to retrieve the desired result set.
Join operator relate the tables on the basis of logical relationship. For joining the tables, at least one column accross the tables must have the same meaning as per business.
We have following type of joins in SQL Server:-

• Inner Join
• Outer Join
• Cross Join
• Self Join

Between

In SQL Server, with the help of "Between" logical operator, we can provide the value range to test expression for evaluation.

Syntax:-

test_expression [ NOT ] BETWEEN <start value/expression> AND <end value/expression>

Note:- BETWEEN consider the range inclusive of start and end value for evaluation of test expression.

Example:-
Create a table and insert some records in table as below:-

Now let's make use of "Between" in our query:-