Introduction: 

In this article I will explain what are the Joins in SQL Server or how to use joins in SQL Server and different types of Joins example (SQL LEFT outer Join, SQL RIGHT outer Join, SQL FULL outer Join, SQL inner Join sample example) and uses of Joins in SQL Server.


Description:

SQL joins are used  to combine rows from two or more tables based on relationship between some of the columns in tables. Basically we will use primary key of first table and foreign key of second table to get data from tables with this relationship we can remove the duplication of data in every table.

There are 4 different types of SQL Server joins:

  1. INNER JOIN
  2. LEFT OUTER
  3. RIGHT OUTER JOIN
  4. FULL OUTER JOIN

Now  I am going to explain below one by one .

I have two table student and fee.

create table Student
(
id bigint identity(1,1) primary key,
Name nvarchar(150),
Mobile nvarchar(100),
address1 nvarchar(500)
)

 

Student Table Records:


create table Fee
(
id bigint identity(1,1) primary key,
Student bigint references Student(id),
ammount decimal(10,2)
)

Fee table records.

INNER JOIN

Inner join Return only both table matching records.


Example            

select * from
Student as s inner
join Fee as f on s.id=f.Studentid

Result:



LEFT OUTER JOIN

  •      The left outer join Return all the rows from the first table and matching rows from the second table.


Example:

select * from Student as s left outer join Fee as f on s.id=f.Studentid

Result:

RIGHT OUTER JOIN

The right outer join Return all the rows from the second table and matched rows from the first table.


Example:       

select * from
Student as s right
outer join Fee as f on s.id=f.Studentid

Result:


FULL OUTER JOIN

Full Outer Join Return all the matching and non matching rows of both the tables.


Example:

select * from Student as s full outer join Fee as f on s.id=f.Studentid

Result:


Thanks.

0 comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Welcome to SQL Tutorials Point

Don't have an account? Click here to Create a new account, it takes less than a minute.