Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Friday, 22 April 2016

JOIN In SQL

 inner-join, Joins, Left-join, right-join, SQL     No comments   

SQL JOIN clause is used to work with  two or more tables, it combine rows from two or more tables, based on a common field between them.By using Joins we can fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join.


Database Design For JOIN

create table Employee(
id int identity(1,1) primary key,
Username varchar(50),
FirstName varchar(50),
LastName varchar(50),
DepartID int
)

create table Departments(
id int identity(1,1) primary key,
DepartmentName varchar(50)
)

after creating the table design of two tables insert data according to your need. 


Different Types Of JOINS 

Inner JOIN
Inner JOIN is used when we need to retrieve only same record from both tables .It returns only the matched record from both tables. 
Example:
select e.UserName, e.FirstName,e.LastName from Employee e inner join Departments d on e.DepartId=d.id
Or
select * from Employee e inner join Departments d on e.DepartId=d.id

Outer JOIN
Outer join returns all the rows of both tables whether it has matched or not. Outer JOIN's has three types

1. Left Outer JOIN
2. Right Outer JOIN
3. Full Outer JOIN

Left Outer JOIN
Left outer join returns all records(rows) from left table and from right table it returns only matched records. If there are no columns matching in the right table, it returns NULL values.
Example:
select * from Employee e left outer join Departments d on e.DepartId=d.id

Right Outer JOIN
Right outer join just returns all records(rows) from right table and from left table it returns only matched records. If there are no columns matching in the left table, it returns NULL values.
Example:

select * from Employee e right outer join Departments d on e.DepartId=d.id

Full Outer JOIN
Full outer join combines left outer join and right outer join. This join returns all records from both the tables.If there are no columns matching in the both tables, it returns NULL values.
Example:
select * from Employee e full outer join Departments d on e.DepartId=d.id

Except these two other JOIN's SQL supports but these JOIN not used in daily practice

Self JOIN
In a self join we are joining the same table to itself by essentially creating two copies of that table.When we do a self join, the table names absolutely must use aliases otherwise the column names would be ambiguous.
Example:
select e1.Username as Profile_Name, e2.FirstName as Name from Employee e1 inner join Employee e2 on e1.FirstName=e2.LastName

Cross JOIN
It returns a set of records  which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
Example:

Select * from Employee cross join Departments
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

comma seperated string in sql Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS row_number() and dense_rank() salary table structure. Scope_Identity() Use of indexes while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write