joins:

joins are used to combine rows from two or more tables.

there are a list of joins available in the SQL. they are:

-inner join. , this will return the matched rows of both the table if atleast one was matched.

-left outer join. - return all rows from left table and matched records in the right table.

-right outer join. - return matched records in the left table and all records in the right table.

-full join. - returns all rows from the left table and from the right table.

-self join - this is used to compare the values of the table itself

for testing the joins lets create two table customers and orders

customers (TABLE)

ID int(10)

LastName varchar(255)

FirstName varchar(255)

Address varchar(255)

City varchar(255)

orders (TABLE)

OID int identity(10)

customerid int(10)

amount int(10)

the syntax for creating above tables is:

CREATE TABLE customers

(

ID int PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

CREATE TABLE orders

(

OID int IDENTITY(1,1) PRIMARY KEY,

customerid int not null foreign key references customers(id),

amount int

);

Let's consider the values of these tables as

table (customers):

ID LastName FirstName Address City

1 kumar kishore arumbakkam chennai

2 arul aravind choolai chennai

3 venu visali bommannali bangalore

table (orders):

OID customerid amount

1 2 2500

2 1 4510

3 1 100

4 2 800

5 3 250

6 1 9500

INNER JOIN:

THE CODING FOR THE INNER JOIN.

which gives us the following output

from the above output it can be viewed that as said earlier the inner join will get all the values when there is a match with a field both in common.

LEFT OUTER JOIN:

in the left outer join, the result will brings out all the values of the left table (i.e) customer table and if the value for the left table is not available with the right table (i.e.) orders then it will show the value 'null'for the fields.

as like below:

RIGHT OUTER JOIN:

in the right outer join, the result will brings out all the values of the right table (i.e) customer table and if the value for the left table is not available with the right table (i.e.) orders then it will show the value 'null'for the fields.

like this is same as the left outer join but in the opposite way.

the result will be as follows, in this ex, the orders table is having the foreign key in reference with the primary key of customers table and hence it doesn't have any values for customerid otherthan mentioned in the customers table.

FULL JOIN:

full join will put all the values of both the tables, and fills the column that doesn't have any values with 'null'.

self join:

self join is a special purpose join which is used to compare the table with same table by assuming it as a second table by temporarily giving it a name.

eg: for the self join, in which the employee id, employee name and employee under supvision was mentioned , the third field gives you the detail like that employee under control of whom.

the below table shows the view with the employee id, employee name and employee under supvision after entering the values.

the below view shows us that the employee was under whose control, for these purpose the self join can be used , but this uses the keyword of the 'left outer join', and this is called the self join as because the table is compared with the table itself.

sub query:

the sub query is the term araised as because this query was written inside the another query. like the search result of one query will be given as the input to another query to get the desired output.

THE OUTPUT CAN BE VIEWED @ http://sqlfiddle.com/#!3/93680/19

below mentioned the example of the sub query:

in this example we considered to find the third highest salary in the table for which the above sub query was retured which gives the value like

it actual gives the right value of the third highest salary in the table.

UNION:

The union keyword combines all the fields mentioned in the first and second table. the things that to be followed while using the union keyword is that the no of fields mentioned and the data types of the fields mentioned should be same. when doing that the fields of both the tables will be combined and will show us the result.

*********************************************************************************