Unit 5 Discussion Question

Discuss the concept of a join operation.  As part of your discussion, compare and contrast the inner

join, left outer join, right outer join, and full outer join.   Provide examples of how each can be used.

UNION

A JOIN operation concatenates(Combines) two relations based on a certain joining condition or predicate.The two relations must have a common attribute with the same underlying values which facilitates the joining condition. 

The formal notation for joining operation is :

R<join condition>  S

For two relations R and S we can now define the formula as follows

1.<Join condition> =<attributes from  R> <comparison  operator> < attribute from S>

2.The comparison operator can be either  <,>,<=,>=,=,<>,

I will use the following two tables ,A and B,  and visual diagrams to drive home the concepts of left outer join,right outer join and full outer join.

Table A(Left)

Car Relation

Car_ID	Model	Manufacture YEAR	Color	Registration Date(YEAR)
010	Honda	2005	Blue	2007
011	Isuzu	2006	White	2009

Table B(Right)

CarOwner Relation Ship

Name	Address	Phone Number	email	Car_ID
james	Juja	123456	james@kenyaone.com	010
Jane	Thika	123457	jane@africanoline.com	012

Left Outer Join

The following statement

SELECT*

FROM CAR

LEFT OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

          Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	Isuzu	2006	White	2009
-	-	-	-	012	-	-

  

Right Outer Join

The following statement

SELECT*

FROM CAR

RIGHT OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	-	-	-	-
Jane	Thika	123457	jane@africanoline.com	012	-	-	-	-

Full outer Join

The following statement

SELECT*

FROM CAR

FULL OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	011	Isuzu	2006	White
Jane	Thika	123457	jane@africanoline.com	012	-	-	-	-

Unit 5 Discussion Question

Discuss the concept of a join operation.  As part of your discussion, compare and contrast the inner

join, left outer join, right outer join, and full outer join.   Provide examples of how each can be used.

UNION

A JOIN operation concatenates(Combines) two relations based on a certain joining condition or predicate.The two relations must have a common attribute with the same underlying values which facilitates the joining condition. 

The formal notation for joining operation is :

R<join condition>  S

For two relations R and S we can now define the formula as follows

1.<Join condition> =<attributes from  R> <comparison  operator> < attribute from S>

2.The comparison operator can be either  <,>,<=,>=,=,<>,

I will use the following two tables ,A and B,  and visual diagrams to drive home the concepts of left outer join,right outer join and full outer join.

Table A(Left)

Car Relation

Car_ID	Model	Manufacture YEAR	Color	Registration Date(YEAR)
010	Honda	2005	Blue	2007
011	Isuzu	2006	White	2009

Table B(Right)

CarOwner Relation Ship

Name	Address	Phone Number	email	Car_ID
james	Juja	123456	james@kenyaone.com	010
Jane	Thika	123457	jane@africanoline.com	012

Left Outer Join

The following statement

SELECT*

FROM CAR

LEFT OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

          Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	Isuzu	2006	White	2009
-	-	-	-	012	-	-

  

Right Outer Join

The following statement

SELECT*

FROM CAR

RIGHT OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	-	-	-	-
Jane	Thika	123457	jane@africanoline.com	012	-	-	-	-

Full outer Join

The following statement

SELECT*

FROM CAR

FULL OUTER JOIN      CarOwner

ON CAR.CAR_ID=CAR_ID  

Will return the following table

Name	Address	Phone Number	Email	Car_ID	Model	Manufacture Year	Color	Registration Year
James	Juja	123456	james@kenyaone.com	010	Honda	2005	Blue	2007
-	-	-	-	011	011	Isuzu	2006	White
Jane	Thika	123457	jane@africanoline.com	012	-	-	-	-