Types of joins

Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
 Cross_Join

INNER JOIN

The inner JOIN is used to return rows from both tables that satisfy the given condition.

inner_Join

Inner_Join_Example

Inner_Join_Example_op

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural_JoinNatural_Join_OP

Outer JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.

Left_Outer_JoinLeft_Outer_Join_op

Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.

Right_Outer_Join.PNG

Right_Outer_Join_op1.PNG

Full Outer Join

The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.

Full_Outer_Join

Full_Outer_Join_OP.PNG

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