Monday, June 25, 2012

Joins in SQL Server AND My sql

Joining tables in SQL Server and My sql is equal, so there is no need to confuse here.
Ket us start with creating 2 tables with the database name as family

1. Create Table  name as adults as shown below

create table adults
(
    adultid int primary key identity(1,1),
    adultname varchar(250)
)
2. Create another Table  name as child as shown below

create table child
(
    childid int primary key identity(1,1),
    childname varchar(250),
    adultid int
)


Still now we just created 2 tables  in family database.
Let us insert data into these tables directly from sql server management studio.

As the TITLE says we need to join tables. Before that we should know how many types of joins are there?
      Totally there are  6 types are there. They are :- 
       1. INNER JOIN 
          2.  LEFT OUTER JOIN 
          3.  RIGHT OUTER JOIN 
          4.  FULL OUTER JOIN 
          5.  CROSS JOIN
       6. SELF JOIN
         
         In these joins mostly and commonly used joins are first 3 joins only and rarely the remaining. 


1. INNER JOIN :-
INNER JOIN means shows common fields in both the tables  based on foreign key and primary key and non empty rows.

select a.adultname, c.childname from adults a inner join child c on a.adultid = c.adultid
2. LEFT OUTER JOIN :-
LEFT OUTER JOIN means to show all records from left table (i.e. one that precedes in SQL statement) regardless of the existance of matching records in the right table.

select a.adultname, c.childname from adults a left outer join child c on a.adultid = c.adultid
3. RIGHT OUTER JOIN :-
RIGHT OUTER JOIN means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.

select a.adultname, c.childname from adults a right outer join child c on a.adultid = c.adultid 
and finally the out put for theses  3 joins is as shown in the figure below...
























4. FULL OUTER JOIN :-
FULL OUTER JOIN means shows all the rows from both the tables
select a.adultname, c.childname from adults a full outer join child c on a.adultid = c.adultid
5. CROSS JOIN :-
CROSS JOIN means shows total rows as multiple (no of rows from table 1 * no of rows from table 2)

select a.adultname, c.childname from adults a cross join child c

Out put for these two joins as shown in the image

 

No comments:

Post a Comment