SQL JOINS

I am going to explain about the Joins .they are four different types. 

• Left Join
• Right Join
• Inner Join (self-Join)
• Cross join

Left Join 

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

 

 

 

 

ProjectUID	ProjectTitle	ProjectType
14	New Home Dome Project	NULL
20	No Project (SOUTHERN)	NULL
25	Cypress Pipeline Project	NULL
50	South System Expansion III	Loop
50	South System Expansion III	Replacement

Example:   SELECT p1.[ProjectUID]

      ,p1.[ProjectTitle] 

         , s1.ProjectType    

  FROM [ROWDMAP].[dbo].[tblProjects] p1

  left join tblProjectProjectTypes p2   on p1.ProjectUID=p2.ProjectUID

  left join tblStaticProjectType s1 on p2.ProjectTypeNo =s1.ProjectTypeNo

  where p1.DataOriginGroup='SOUTHERN'

Right Join: 

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

 

 

 

 

 

 

ProjectUID	ProjectTitle	ProjectType
50	South System Expansion III	Loop
50	South System Expansion III	Replacement
245	South System IV	Loop
245	South System IV	Greenfield
245	South System IV	Replacement

 

Example:  SELECT p1.[ProjectUID]

      ,p1.[ProjectTitle] 

         ,s1.ProjectType    

  FROM [ROWDMAP].[dbo].[tblProjects] p1

  right join tblProjectProjectTypes p2   on p1.ProjectUID=p2.ProjectUID

  right join tblStaticProjectType s1 on p2.ProjectTypeNo =s1.ProjectTypeNo

  where p1.DataOriginGroup='SOUTHERN'

 

Inner Join:

The SQL Inner JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.

ProjectTitle	ScopeOfWork
New Home Dome Project	Washington County to Yuma County, Colorado
No Project (SOUTHERN)	Washington County to Yuma County, Colorado
Cypress Pipeline Project	Washington County to Yuma County, Colorado
South System Expansion III	Washington County to Yuma County, Colorado
Elba Express	Washington County to Yuma County, Colorado
New Home Dome Project	NULL
No Project (SOUTHERN)	NULL
Cypress Pipeline Project	NULL
South System Expansion III	NULL

Example :

select p1.ProjectTitle,p2.ScopeOfWork    From tblProjects p1 inner join tblProjects p2

on p1.ProjectUID <>p2.ProjectUID

where    p1.DataOriginGroup='SOUTHERN' and p2.CompanyNo =22

 Cross Join:

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.

 

 

 

  

ProjectTitle	ProjectTypeNo
0001-A-Abandoned	7
106-recoat 101-1 to 106-1 CAO	1
2010 PIP Firefly OH	6
2012 Anom Rem (261-2 to 267-2)	7
300 Line Expansion	2
300 Line Expansion	8
342-Expose Pipe @ Easton, CT	6
4240 - Marlin Midstream	5
4243 - Lymac Exploration	5
4250 - Cheif Gathering Elliot	5

 

 

Example :

select p1.ProjectTitle,p2.ProjectTypeNo    From tblProjects p1 cross join tblProjectProjectTypes p2

where  p1.ProjectUID =p2.PRojectUID