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:


SQL Joins

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

Fee table records.

SQL Joins

INNER JOIN

Inner join Return only both table matching records.

SQL Joins

Example            

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

Result:

SQL Joins


LEFT OUTER JOIN

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

SQL Joins

Example:

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

Result:

SQL Joins

RIGHT OUTER JOIN

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

SQL Joins

Example:       

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

Result:

SQL Joins

FULL OUTER JOIN

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

SQL Joins

Example:

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

Result:

SQL Joins


Thanks.

comments


Leave a comment

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

You may also like