Technofriends

Joins are one of the most dreaded terms to the students trying to learn database principles. Many of them ( whom i have interacted with in past) are not very clear about these concepts. For all those who have till date felt dreaded with this term, this post should be of help.

I am assuming that you are atleast aware of SQL Statements related to CREATE, SELECT, INSERT and UPDATE.

A join is a temporary relationship that you can create between two tables in a database query that do not already have an established relationship or common field with the same fieldname or data type. Database tables that are joined in a query are related in that query only, and nowhere else. The type of join that you use indicates which records the query will select or perform the chosen actions on.

Joins can be broadly classified as

• Inner Joins
• Outer Joins

Inner Joins

Inner Joins are also referred as Equi Joins. It selects only those records from the two tables which have matching values. The figure below uses a diagrammatic approach to make this even more clear

In order to understand this example in a better way, lets assume that we have two tables, one lists Plumbers [table "0" not found /]
and the other lists Electricians [table "0" not found /]
. Therefore, analyzing the diagram above, we can say that in this system, there are some Plumbers who are also Electricians ( depicted by the common area).

In such a scenario, the SQL query which retrieves all those plumbers who are electricians as well can be written as:

SELECT * FROM A INNER JOIN B ON A.SID=B.SID

SID is the social identity number ( assuming every individual has a unique number)

Outer Joins

Outer Joins unlike Inner Joins select all the records from one database table and only those records from the second table which have a matching value in the joined field. Therefore, in a left outer join, the selected records include all the records from the first table and in case of a right outer join, all records from the second table shall be selected.

Left Outer Join

The above figure is a diagrammatic representation of what is mentioned above for the left outer joins.

In order to understand it better, lets take another example; Let table A be the Plumbers and table B be the electricians. Now if you wish to write a query which enlists all the plumbers and also those electricians which are plumbers you will need a Left Outer Join.

The query can then be written as

SELECT A.NAME,B.NAME FROM A LEFT JOIN B ON A.SID=B.SID

Right Outer Joins

As already mentioned above, in case of a right outer join, all those records which are from second table and those records from first table which meet the matching criteria are selected as results.

If continuing with the above mentioned example ( Plumber and Electrician example), we have a scenario wherein we are required to list down all the Electricians and only those Plumbers who are also Electricians, we shall make use of Right Outer Join.

Therefore in such a scenario the SQL Query will look like this

SELECT A.NAME, B.NAME FROM A RIGHTJOIN B ON A.SID=B.SID

Hope this post helped you get a better idea about Joins and why are they used in Databases.
I am also putting below some links which i believe can be great reads for all those interested

W3Schools Tutorial on Joins

DatabaseDev

Do stay tuned to Technofriends for more, one of the best ways of doing so is by subscribing to our feeds. You can subscribe to Technofriends feed by clicking here.

Cheers

Vaibhav

Related posts:

1. [Wordpress] Optimize and Repair your MySQL Database Using phpMyAdmin
2. Manage SQLite Database on your PC with SQLite Manager
3. [How-To] Create and Delete Users in MySQL
4. [How-To] Delete Wordpress Spam Comments Using phpMyAdmin
5. Google Code University lets you learn programming the Google way