Wednesday , January 17 2018

JOINS

JOINS: Joins in SQL Server explained bellow (one by one):-

These are used for retrieving the data from one or more tables at a time.Joins can be used in any of the following ways.

1.Equi-joins

2.Non-equi-joins

3.Self join

4.Cross join

5.outer join

Sample Tables:

Table 1:EMPLOYEE

 

Table 2: DEPARTMENT

 

1.Equi-joins:-  If two or more tables are combined using in equality condition we call it as equi-join.

Example:

2.Non-equi-joins:- If we join tables with any condition other than equality condition we call it as a Non-Equal-join.

Example:

3.Self join:- Joining a table to itself by providing two table alias names is called SELF-JOIN.

Example:

4.Cross join:- If two or more tables are combined with each other without any condition we call it as a Cartesian (or) a cross Join. Here each row of the first table goes and joins with each row of the second table. So if the first table as m rows and the second table as n rows. Simply it is the multiplication of two tables.

Example:

 

5.outer join:- It is the extension of Inner Join operation because Inner selects only matched records from multiple tables where Outer join selects matched records as well as unmatched records. It includes.

a.Left Outer Join

b.Right Outer Join

c.Full Outer Join

a. Left Outer Join:- Retrieves the matching data from both the table as well as unmatched data from   Left hand side table.

Example:

b.Right Outer Join:- Retrieves the matching data from both the table as well as unmatched data from Right hand side table.

Example:

c. Full Outer Join:- Retrieves the matched data from both the tables plus unmatched data from L.H.S tables plus unmatched data from R.H.S tables also.

 

Example:
Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Samba

Check Also

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …

%d bloggers like this: