usage of joins

Question

usage of joins

selvakumar.sms

SSC Eights!

Points: 895
More actions
May 12, 2012 at 11:44 am

#261281

hi
please explain me where to use right outer join and left outer join.
i referred many articles but i couldn't get clear idea about joins in sql.
please give ur precious time for simple basic question.
please explain me!!!
thanks
--------------------------------------------------------
I am Learner -- SQL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

I am going to start by asking you to explain to me what you think are the uses of left or right outer joins. Can you explain to me in basic terms what each does? Once I have a better idea of what you do know, the better answer I can give you, hopefully.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

selvakumar.sms (5/12/2012)
hi
please explain me where to use right outer join and left outer join.
i referred many articles but i couldn't get clear idea about joins in sql.
please give ur precious time for simple basic question.
please explain me!!!
thanks

There's really no difference between the two other than which table will have all the rows shown for it.

The following code will return all of the rows for TableA and the rows from TableB that match. Since "TableA" in on the left side of the word join, the LEFT OUTER JOIN correctly describes that all of the rows from TableA will be returned.

FROM TableA LEFT OUTER JOIN TableB ON some condition

Conversely, the following code will return all of the rows from TableB because it is on the RIGHT side of the word JOIN.

FROM TableA RIGHT OUTER JOIN TableB ON some condition

When should you use a RIGHT OUTER JOIN instead of a LEFT OUTER JOIN? There are some logical reasons but mostly it's when you want to drive people who don't know the difference nuts. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

selvakumar.sms SSC Eights! Points: 895 More actions · Answer 3

hi lynn,

left outer join - take all values from left table and match with right table, if value not match with right table that row comes with null.

right outer join - take all values from right table and match with left table,if value not match with left table that row comes with null.

basically atleast one field (column) common in 2 tables then only we can use join.

lynn i dont know where to use inner joins, outer joins , left and right joins.

please advice !!

--------------------------------------------------------

I am Learner -- SQL

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Perhaps a bit of a join tutorial is in order.

http://www.w3schools.com/sql/sql_join.asp

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 5

This might help you

http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join

http://www.databasejournal.com/features/msaccess/article.php/3516561/Implementing-the-Equivalent-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

selvakumar.sms (5/12/2012)
Every experts was once a beginners!!! every pros was once an amateur

Just a suggestion... While what you say (as quotes above) may be true, the difference between high paid experts and others is that the high paid experts usually got very comfortable with Books Online (the free help system that comes with SQL Server) and how to do web searches instead of posting a question and waiting for someone to answer it for them. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions