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

Popular Posts

  • Base64 Encoding And Decoding In SQL Server
    Introduction In this article we will learn How to encode a string into Base64String and how to decode Base64String to string. How to crea...
  • Convert DataSet To List Or GenericList Using LINQ - C#
    Introduction In this article we will learn how to convert dataset to list or convert DataSet to List of collection o generic list in c#. ...
  • Trigger In SQL Server - Insert Update Delete In Single Trigger With Example
    Introduction In this article we will learn what is trigger . How to manage Insert, Update and Delete operation in trigger. Insert Update ...
  • Maintain The State Of Dynamic Added User Control On Each PostBack
    Introduction In this article we will learn how to maintain the state of dynamically created controls in asp.net using c#.  Retain the stat...
  • What Is Connection Pooling In Asp.Net Using C# With Example
    Introduction In this article we will learn what is connection pooling. Why connection pooling useful , what is the max limit and minimum ...

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

Best Deal Here

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