JOINS IN SQL

Sometimes, its very hard to keep Joins concept in mind.. Being a beginner, experienced SQL programmer, you should know the concept of JOINS.

In this article, I will show you the difference between:
1. JOINS and INCLUDE
2. How to you use JOINS in rails
3. JOINS over INCLUDE
4. JOINS and INCLUDE in RAILS-3

Let’s cover them in detail:
What is Join ?
The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.

Joins Type
1. OUTER JOIN
1.a. LEFT OUTER JOIN
1.b. RIGHT OUTER JOIN
2. Inner Join

Let’s take an example here:

<images comes here />

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn’t made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

So, far you must be cleared with what JOIN does!! Now, we will see what JOINS and INCLUDE gives us….

Consider these two statements:

User.find(:first, :include => :user_profiles)
User.find(:first, :joins => :albums)

<I am going to update this post soon.>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s