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.
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.
*********************************************************************************
