What are JOINS in Database?

Joins

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

Inner Join

In order to understand this example in a better way, lets assume that we have two tables, one lists Plumbers [table A] and the other lists Electricians [table B]. 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

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

Right Outer Join

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

Advertisements

Hindi Programming Language

SKTN Hindi Programming Language (HPL) is a high level programming language with the distinguishing feature of being in Hindi (primary language of India) developed by the Indian developer Shamit Kumar Tomar. The language is currently in its initial stage and is developing continuously. The first beta of the language and the accompanying IDE (Integrated Development Environment) is released on August 29, 2007.

The Hindi Programming Language has been developed to target the over 350 million Hindi speaking population. According to Comrie (1998 data),[8] Hindi is the second most spoken language in the world.

The Hindi Programming Language is designed to be easy to learn and use. The language not only allows programmers to easily create simple applications in their native language, but also has the flexibility to develop fairly complex applications as well. The programs are compiled to Microsoft Intermediate Language (MSIL) that can run on any machine where .NET Framework 2.0 is installed.

Check out http://sktn.freehostia.com/ for more details.

Program to print Hello World on screen:

कार्य खाली मुख्य ( )
     लिखें "Hello World!"
खत्म कार्य

Program to input time from user and greet him/her accoordingly:

कार्य खाली मुख्य ( )      अंक समय       लिखें "Enter current time"
     पढें समय        अगर [ समय < 12 ]
        लिखें "Good Morning"
  वरनाअगर [ समय >= 12 और समय < 16 ]
           लिखें "Good Afternoon"
  वरना              लिखें "Good Evening"
  खत्म अगर
  खत्म कार्य

Check out http://sktn.freehostia.com/ for more details. 

Cheers,

Vaibhav

Excellent list of resources for Web2.0

Just came across an excellent resource list on Web 2.0 Stuff compiled by Everything Web2.0 blog. The list has amazing resources to help you build better.

Probably the most interesting ones are the frameworks, platforms and libraries for typical 2.0-stuff .
But watch out for all other pearls: bug & issue trackers, business intelligence solutions, database solutions, catalogue wares, design toolboxes, enterprise solutions, media editors, hosting & domain management & testing tools, media management solutions, mobile content delivery tools, site tour builders, hardcore telephony solutions, voice recognition tools, and more incidental code snippets, like URL shorteners, widget builders, Ajax & RIA apps.

Check out the complete list here

Cheers,

Vaibhav