Saturday , August 18 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

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …