use of right outer join and left outer join

Question

Can some one give me a practical example of right outer join and left outer join.

I am doing the sql course, I know the theory but it does not make an sense. Is it used in the real world ?

Answer 1

Yes it is used in real world. In fact, it's the function I use most in my work.

The basic concept here is that with LEFT JOIN, you want the contents of the second table to join with the first table. And with RIGHT JOIN, you want the contents of the first table to join with the second table.

There's a catch though, if you want a good join then there should be at least one column that both tables have that contains matching data. A good example would be a numbered (NO) column or a name column.

Example (SQL):

table1:
No. Name XP
1 Gavin 25200
2 sneeze 2048

table2:
No. Name Level
1 Gavin 16
2 sneeze 7

//Command
SELECT table1.*, table2.Level FROM (select * from table1) AS table1 LEFT JOIN (select Name, Level from table2) AS table2 ON table1.Name = table2.Name

//Output
No. Name XP Level
1 Gavin 25200 16
2 sneeze 2048 7

//Summary
-Notice how I only selected Level from table2, because I wanted to join on name and add Level.
-Notice how there wasn't two Name columns in the output, because the column you join on gets merged with the first and only the remaining column gets added.
-Notice how the Level column got added at the end, because I did a LEFT JOIN and since I selected all columns from table1, the Level column got added at the end.

Answer 2

Thank you for your answer.

I made this statement, to recreate the result without a join

Select Level.*, XP.XP From Level, XP where Level.Name = XP.Name

What is the difference between the statement above and your statement.