SQL JOIN

 JOINS IN SQL SERVER

I am going to explain various type of join in SQL server  like  Inner  Join ,cross join , Left Outer Join, Right Outer Join, Full Outer Join, Equi  Join ,Hash Join.

 

Before going to join discussion first I am going to create two table . One is Employee   another one is Location.

Employee :

 

EmpId First Name Last Name DOJ Location ID
1 Alok Kumar 2001-01-01 00:00:00.000 1
2 Rahul Raj 2002-08-12 00:00:00.000 1
3 Pankaj Raj 1999-09-01 00:00:00.000 2

 

Location:

 

Location ID Street City State
1 545 Pike Delhi KR
2 222 Gama Bangalore DL
3 Bank Street Kolkatta WB

 

 

 

  1. INNER Join :-

The join that display only those record that have a match in  both joined table is known as inner join. This is default join.

Syntax:

SELECT a1.column_name,a2.column_name

FROM table1  a1

INNER JOIN table2 a2

On a1.colum_name=a2.column_name

 

Example:

select   A.FirstName,L.Street from   Employee  as A

inner join Location L

on a.LocationID=l.LocationID

 

Output:

FirstName Street
Alok Delhi
Rahul Delhi
Pankaj Banglore

EXCEPT vs. LEFT OUTER JOIN

The EXCEPT operation was added to SQL Server in 2005. The operation compares the results of two sets. The results of the first set returns a list for every DISTINCT row that does not have a matching row in the second set.
This is the equivalent of executing a SELECT DISTINCT from some object, joining to a second set using a LEFT OUTER JOIN, and specifying that the outer table record be null.
Both queries perform the same function. When, then, is the EXCEPT operation necessary? Perhaps it makes things easier to understand when working with sets.