What is the purpose of left and right outer join in SQL?

Question

Rather than the technical definition of preserving the left and right tables respectively...What is the purpose of it and where exactly this concept is used? Can someone explain me with a real time example

Answer 1

you would use an outer join when you are not sure that there will be a matching ID variable in the table you are joining to. This way you keep the rows of the original table intact.

example: select * from A left outer join B on A.id =B.id

if A has id 2 and b doesn't then the row with A id 2 will still appear in the output, but with all of the B columns being NULL. If you did the same thing with an inner join you would lose the row with A id 2 result.

Answer 2

When we are working on a complex project that time we normally use joins.

In complex project we have lots of tables and we need two or more table data in single query. Like we have a User table and, in this table we have user basic information. Second table is User Educational Detail. We are creating a user profile and we want to show both information and, In this case we need to use joins. Both table connected by a key.Primary key.

SELECT User.Name, Edu_Detail.University_Name FROM User
RIGHT JOIN Edu_Detail ON User.ID=Edu_Detail.User_ID;

In this example we are getting user name from user table and university name from Edu_Detail table. In user table ID is primary key and in Edu_Detail User_ID is have value of that id.

Answer 3

For example,
I have students_tbl:
ID NAME
1 David
2 John

and grades_tbl:
GRADES STUD_ID
97 1
86 2
95 1
78 1
96 2
89 2

then you want to get all the grades of John.
An output similar to this:
NAME GRADES
John 86
John 96
John 89

You need JOIN to get an output like that.

Answer 4

SQL joins are used to combine rows from two or more tables.
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Answer 5

The most simple explanation to this is that when when you want the contents on one table to have a higher priority over other. For example

Let there be two tables name and toys
We want the new table must contain the name of all the kids either they have a toy or not. The we will use left or right tables accordingly which side they are written