inner join and outer join

[syedwna]

what is this inner join and outer join in mssql server

give me some example

[ranamk]

Joins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

For example, here is an inner join retrieving the authors who live in the same city and state as a publisher:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC


hope this will help
best regards,
rana

[ranamk]

Using Left Outer Joins

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname au_lname pub_name
-------------------- ------------------------------ -----------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems

(23 row(s) affected)

The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

Using Right Outer Joins

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname au_lname pub_name
-------------------- ------------------------ --------------------
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books

(9 row(s) affected)

An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold fewer than 50 copies:

USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
ON s.title_id = t.title_id
AND s.qty > 50
ORDER BY s.stor_id ASC

Here is the result set:

stor_id qty title
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets
(null) (null) Emotional Security: A New Algorithm
(null) (null) Fifty Years in Buckingham Palace Kitchens
7066 75 Is Anger the Enemy?
(null) (null) Life Without Fear
(null) (null) Net Etiquette
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
Mediterranean
(null) (null) Prolonged Data Deprivation: Four Case Studies
(null) (null) Secrets of Silicon Valley
(null) (null) Silicon Valley Gastronomic Treats
(null) (null) Straight Talk About Computers
(null) (null) Sushi, Anyone?
(null) (null) The Busy Executive's Database Guide
(null) (null) The Gourmet Microwave
(null) (null) The Psychology of Computer Cooking
(null) (null) You Can Combat Computer Stress!

(18 row(s) affected)

For more information about predicates, see WHERE.

Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ 2000 provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. The following is the query and results of the Transact-SQL full outer join:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname au_lname pub_name
-------------------- ---------------------------- --------------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books

(30 row(s) affected)



note: all the above data are from sql help

best regards,
rana

[syedwna]

u mean to say like this

student table------>st

uid name
1 syed


course table--------->cr

uid course
1 maths

select st.name,cr.course from st innerjoin st.uid=cr.uid

[ranamk]

select st.name,cr.course from st innerjoin cr on st.uid=cr.uid

ya thats it

[syedwna]

i understood now in ur one line more than any google or yahoo stuff

thanks thanks

in the same way for the above example can u write for other joins,it will help not for me only but whoever reads this thread

Regards
Syed

[ranamk]

you are welcomed

now i will post a full example on all (using your tables but with more data to show the different output)

student table------>st

uid name
1 syed
2 nana
3 bob
4 jack


course table--------->cr

uid cid course
1 1 math
2 1 math
NULL 2 Physics


innner join:
select st.name,cr.course from st innerjoin cr on st.uid=cr.uid
will give:
syed math
nana math


left outer join:
select st.name,cr.course from st LEFT OUTER JOINcr on st.uid=cr.uid
will give:
syed math
nana math
bob NULL
jack NULL

right outer join:
select st.name,cr.course from st RIGHT OUTER JOIN cr on st.uid=cr.uid
will give:
syed math
nana math
NULL Physics


outer join:
select st.name,cr.course from st FULL OUTER JOINcr on st.uid=cr.uid
will give:
syed math
nana math
bob NULL
jack NULL
NULL Physics


best regards,
rana

[syedwna]

i understood complety what u wrote

1)inner join ok
2)outer join

what is the use of outer join because it will give the values where the ids doesnt match whether it is left or rigth or full

[ranamk]

good question,
in fact it didnt happen that i have ever needed any kind of outer join,
but being there it should have an application; right?

[syedwna]

Thanks a loooooooooooooooooooooooooooooooooooooooooooooot


For ur kind cooperation to solve my question..for helping me and my friends to understand better

thanks once again


iam posting now the new thread

[ranamk]

you veryyyyyyyyyyyyyyy welcomed
and guess what !!
i guess we could have cases where we need outer join
i have one in mind just right now...

lets see this example:
if we have two tables: Suppliers and Orders
and if we wat to get all suppliers who do have orders and also who dont but mentioning the orders for the one who do have
so i guess here we will be needing the left outer join as follows:


select suppliers.supplier_name, orders.order_description
from suppliers left outer join orders
on suppliers.supplier_id = orders.supplier_id

regards,
rana

[syedwna]

Thanks


this thread clarrified all my doubts in a basic 4 lines of code


Thanks