SQL Joins

At times we feel..hey! wat's the big deal about joins... still many of us are very much afraid of using them in our day to day coding needs. Let's try to familiarize ourselves again with Joins and make them our friends for life.

What are Joins?
As the name say they help us in joining data in one Table to the data in another one.                As a matter of fact you can Join one table to n number of other tables.

Why we need Joins?
In a database, data is stored in the form of tables. These tables are created keeping in mind the theories of normalization and data redundancy. But at times we might need de-normalized data or a data which we cannot fetch directly from a single table.  
Joins are something which come for our rescue in such situations.

How Joins work?
Let me ask you a simple question. What is at the very core of every database design?...
Yes, you are right! .. It's the relationship between tables? 
Joins, also use them(the relationship) to satisfy our data needs.
Let me explain you with a very simple example. Consider the students of a school participating in different events in a college cultural fest. The below two tables give the details.

Not every student participates in the fest and the participation data(the Event Table) with the college also has entries for some students who left the college.

  Student

ID	Name	Address
1	Tom	A1
2	Carry	A2
3	Dorra	A3

   


  Event

ParticipationID	EventCode	StudentID
1	E1	1
2	E2	2
3	E3	1
4	E4	4
5	E5	5

The Principal of the college asked the Event committee to give a report on below items.

1. The list of the final participation considering students who left the college and those
     who are not participating.
2. The participation details of all college students.
3. The status for all participations in the cultural fest.

The event committee thought of analyzing the items one by one.

1. The list of the final participation considering students who left the college and those
     who are not participating.
    They said it's simple... Pick a students in the Student Table, get all it's entries in the Event
    Table. If there isn't any entry then leave the student. 
     The committee head said..you are right but don't do this manually, use
    Inner Join which will give you the same result.   
        
    select s.Name, s.Address, e.EventCode
    from Student s INNER JOIN Event e ON s.ID = e.StudentID 

Name	Address	EventCode
Tom	A1	E1
Tom	A1	E3
Carry	A2	E2

2. The status of participation of all college students:
     They discussed, it's very similar to the case above with the only difference that: If a student
     isn't present in the Event data we keep it's entry but enter NULL against the EventCode
     column. We can make use of "Left Join" for this.

     select s.Name, s.Address, e.EventCode
     from Student s LEFT JOIN Event e ON s.ID = e.StudentID 
  

Name	Address	EventCode
Tom	A1	E1
Tom	A1	E3
Carry	A2	E2
Dorra	A3	NULL

3. The status of all the participations in the college fest:
     They discussed, for this we will have to take the reverse of above cases. i.e. We will pick a
     "ParticipationID" from the Event table and see whether there is any student against it. If there
      isn't any we will keep the "ParticipationID" but fill NULL for Name and Address fields. We
      can use "Right Join" for this.

      select s.Name, s.Address, e.EventCode
      from Student s RIGHT JOIN Event e ON s.ID = e.StudentID 

Name	Address	EventCode
Tom	A1	E1
Tom	A1	E3
Carry	A2	E2
NULL	NULL	E4
NULL	NULL	E5