Basics of SQL - part III(Joins)

As Join is used the most by the Database programmers and the testers, a discussion on this would be very much valuable

What is meant by a join?

Its linking of more than one table on some common columns.

Why joins?

Because you may not get all the information from a single table.

Let me give you an example

Student Table

Topper Table
Lets say that we have two tables. 1) Students 2) Toppers

Lets say that two students have topped in the state. From the above tables you can understand that Shyam and Sonali have topped in the state. But how do we display their names and the scholarships they have received. Here join helps us.

With joins you can combine more than one table and retrieve informations that is required.

For our scnario(where we are interested for the students names and the scholarships they have got) we can go for the inner join.

Inner join links the tables on the common columns. As its visible, the common columns are the Studentid and the Topper id.

The inner join is written like the following

Select a.Name as Student_Name, b.Scholarship
from Student a inner join Toppers b
on a.Studentid=b.topperid

It would return a resultset like the following

Student_Name Scholarship

Now is it not exciting? But would this query be able to help us when we have a need of displaying the information which consists of toppers and the non-toppers. Lets say the principal is interested to see the students who have topped and what scholarships they have got and the students who have not topped.
We have to go for a left outer join here

It can be writeen in the following way

Select a.Name as Student_Name, b.Scholarship
from Student a left outer join Toppers b
on a.Studentid=b.topperid

Now the resultset would look like the following

So a left outer join consists of

1) Matching rows
2) All the non-matching rows from the left table ( as defined in the query) with null values for columns that are from the right table


Similarly right outer join consists of
1) Matching rows
2) All the non-matching rows from the right table ( as defined in the query) with null values for columns that are from the right table
Cross join is even another type of join where resultset is formed from all the possible combinations between two tables.