Different types of joins in SQL

Inner Join - Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate

Inner joins is further classified as equi-joins, as natural joins, or as cross-joins.

Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join


Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

Cross join
CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table

Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Left outer join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the right table returns one row and the left table returns more than one matching row for it, the values in the right table will be repeated for each distinct row on the left table.

Right outer join
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). Result of right outer joins can also be produced with left outer joins by switching the table order

Full outer join
A full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

Self-join
A self-join is joining a table to itself.


Refer to http://en.wikipedia.org/wiki/Join_%28SQL%29 for detailed understanding with examples

how to find out data type of a column in a table through RoR code

In one of my application, there is a table, Product with more than 100 columns. I want to find out
- the data type of "product_owner" column
- to iterate over all columns and collect array of boolean / tinyint(1) data type columns.

Here is the code to do that:
Product.columns_hash["product_owner"].sql_type
Above code will give you the data type of the column, in this case its "varchar(255)"

If you know that "product_owner" is the 9th column of the table, then you can also find the data type by command:
Product.content_columns[9].sql_type

To iterate over all columns and collect array of boolean / tinyint(1) data type columns:

b= []

Product.content_columns.each do |p|

  if p.sql_type == "tinyint(1)"

    b << p.human_name

  end

end

Now, when you will print 'b', you will get array of all the fields of data type boolean / tinyint(1)

How to iterate over table columns and get values

In one of my application, there is a table, Product with more than 100 columns. I wanted to iterate over all the columns and display their values on product show page.

This is how I did it:
<table border="1">
    <% for column in Product.content_columns %>
        <tr>
          <th><%= column.human_name %></th> 
          <td><%= h @product.send(column.name) %> <td>
        </tr>
    <% end %>
</table>

column.human_name - Returns the human name of the column name. If your column is "product_owner", it will return "Product owner".

column.name will return the name of the column (product_owner)

@product.send(column.name) will return the value of the column.