| Author: Bunty 27 Jun 2008 | Member Level: Diamond Points : 2 |
Hi,
Nice attempy to explain Join in SQL Server.
If possible explain outer join in brief,you didn't mention anything on outer join.
And also if possible give eample for self join.
Keep posting.
Thanks for sharing your knowledge.
Thanks and Regards
S.S.Bajoria
|
| Author: Sriram 30 Jun 2008 | Member Level: Gold Points : 2 |
joins in sql server
Inner join
Outer join(left outer join,Right outer join)
Full outer Join
Cross join
create table emps(empid int,empname varchar(34))
insert into emps values(103,'ramesh')
create table employee(empid int,salary money)
insert into employee values(104,6000)
select * from emps
select * from employee
inner join:
select * from emps inner join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
(2 row(s) affected)
left outer join :
select * from emps left outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
103 ramesh NULL NULL
(3 row(s) affected)
right outer join
select * from emps right outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
NULL NULL 104 6000.0000
full join :
select * from emps full outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
103 ramesh NULL NULL
NULL NULL 104 6000.0000
cross join:
select * from emps cross join employee
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 101 3000.0000
103 ramesh 101 3000.0000
101 sriram 102 4000.0000
102 ganesh 102 4000.0000
103 ramesh 102 4000.0000
101 sriram 104 6000.0000
102 ganesh 104 6000.0000
103 ramesh 104 6000.0000
To Join different DataBase :
select * from master.dbo.emps inner join msdb.dbo.employee on
master.dbo.emps.empid=msdb.dbo.employee.empid
|
| Author: UltimateRengan 30 Jun 2008 | Member Level: Diamond Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: Anne Grace 23 Jul 2008 | Member Level: Gold Points : 0 |
good one.keep going
|
| Author: deepakyadav 23 Jul 2008 | Member Level: Bronze Points : 0 |
thank u this really helps me pal
|
| Author: deepakyadav 23 Jul 2008 | Member Level: Bronze Points : 0 |
if possible can u please give me some more details on stored procedures
|
| Author: krishna 18 Sep 2008 | Member Level: Bronze Points : 0 |
If possible can u please give me some more details on Inner joins.
|
| Author: Ratnasri 19 Sep 2008 | Member Level: Bronze Points : 1 |
Good Information
If U dont mind Can U give me some information on TRIGGERS and EXCEPTIONS in SQL
|
| Author: rajeshwar 31 Jan 2009 | Member Level: Gold Points : 1 |
This article will be very useful for me. It 's a good explanation for sql. thank u
|
| Author: sangeetha 08 May 2009 | Member Level: Gold Points : 2 |
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:
• The "Employee_ID" column is the primary key of the "Employees" table
• The "Prod_ID" column is the primary key of the "Orders" table
• The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names
Employees Table:
Employee_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari
Orders Table:
Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03
INNER JOIN:
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Example INNER JOIN
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
Result
Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair
LEFT JOIN:
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Example LEFT JOIN
List all employees, and their orders - if any.
SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID
Result
Name Product
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari
RIGHT JOIN:
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Example RIGHT JOIN
List all orders, and who has ordered - if any.
SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID
Result
Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair
|