Joins in sql

Join

To return  a result set, combined from multiple tables joins are implemented .
lets take the  two tables below for study.

 Inner Join

when inner join is applied it selects the rows which matches the join fields in both the table

(simply it is called a join).

query:         

      select s.StudentId,s.TeacherId,s.Name as student_name,t.Name as teacher_name

                      from Student s   inner join Teacher t

                      on s.TeacherId=t.TeacherId 

result:

Outer Join

• Left outer join

Returns all rows from the left table (that means the table specified first in select statement) and only the matching rows from the next table(matching refers to the join fields being equal)

query :

           select s.studentId,s.teacherId,s.name as student_name,t.name as teacher_name 

                      from Student s left join teacher t on s.teacherId=t.teacherId order by t.Name

result : 

• Right Outer Join

  Similar to the left join it yeilds the the result which contain all the rows from the right table(i.e table specified after join) and only the matching rows from the right table
  query:
  select s.studentId,s.teacherId,s.name as student_name,t.name as teacher_name from Student s
               right join Teacher t on t.TeacherId=s.TeacherId
  result:
  

  

  
  
  
  
  Don't confuse Left outer join and left join are same and Right outer join is same to right join.
  
  

  Full Join

  also called full outer join returns the rows when there is a match in one of the table  
  query:
             select s.studentId,t.teacherId,s.name as student_name,t.name as teacher_name            
                    from  Student s  full join Teacher t on t.teacherId=s.TeacherId 
  result :
  

  

  
  
  To Create the above two  tables run the script below by creating a new database and  change the your database to the database you have created:
  USE [your database]
  GO
  /****** Object:  Table [dbo].[Teacher]    Script Date: 08/07/2012 10:57:01 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Teacher](
      [TeacherId] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](100) NULL,
      [Faculty] [nvarchar](100) NULL,
      [email] [nvarchar](100) NULL,
   CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED
  (
      [TeacherId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET IDENTITY_INSERT [dbo].[Teacher] ON
  INSERT [dbo].[Teacher] ([TeacherId], [Name], [Faculty], [email])
      VALUES (1, N'paras', N'.net', N'paras@hotmail.com')
  INSERT [dbo].[Teacher] ([TeacherId], [Name], [Faculty], [email])
   VALUES (2, N'sujan', N'Architect', N'sujan@hotamail.com')
  INSERT [dbo].[Teacher] ([TeacherId], [Name], [Faculty], [email])
   VALUES (3, N'ram', N'php', N'ram@hotmail.com')
  INSERT [dbo].[Teacher] ([TeacherId], [Name], [Faculty], [email])
   VALUES (4, N'harish', N'Rail', N'harish@hotmail.com')
  INSERT [dbo].[Teacher] ([TeacherId], [Name], [Faculty], [email])
   VALUES (5, N'ritesh', N'java', N'ritesh@aaa.com')
  SET IDENTITY_INSERT [dbo].[Teacher] OFF
  /****** Object:  Table [dbo].[Student]    Script Date: 08/07/2012 10:57:01 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Student](
      [StudentId] [int] IDENTITY(1,1) NOT NULL,
      [TeacherId] [int] NULL,
      [Name] [nvarchar](100) NULL,
      [Address] [nvarchar](100) NULL,
      [email] [nvarchar](100) NULL,
   CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
  (
      [StudentId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET IDENTITY_INSERT [dbo].[Student] ON
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
  VALUES (1, 1, N'sushil', N'kathmandu', N'sushil@gmail.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
   VALUES (2, 2, N'raju', N'kathmandu', N'raj@hotmail.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
   VALUES (3, 3, N'bj', N'pulchowk', N'bj@yahoo.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
  VALUES (4, 4, N'david', N'bhaktapur', N'david@gmail.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
   VALUES (7, 1, N'nagesh', N'pokhara', N'nagesh@yaho.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
  VALUES (9, 2, N'Nikesh', N'chitwan', N'nik@gmail.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
  VALUES (10, 1, N'rohan', N'pokhara', N'rhn@hotmail.com')
  INSERT [dbo].[Student] ([StudentId], [TeacherId], [Name], [Address], [email])
  VALUES (11, NULL, N'RamSharan', N'syanja', N'rsaran@gmail.com')
  SET IDENTITY_INSERT [dbo].[Student] OFF
  
  
  
  All the things written above are as per my knowledge.If any mistakes in my writing please let me know . and thank You for reading any queries about the article are heartily welcomed
  Thank You
  sushil