Description
SQL Outer Joins gives back all rows from both the tables which satisfy the join condition alongside lines which don't satisfy the join condition.
Before proceeding to outer joins the user need to learn about Joins ,which provide the details explanation regarding joins.
SQL Outer Joins can be classified into two types:
- Right Outer join
- Left Outer join
Description
The condition is optional for the table on right side, where as it is compulsory on the table on right side.
Conceptual
figure
Syntax
SELECT * FROM table1 Right [ OUTER ] JOIN table2 ON table1.column_name=table2.column_name;
Table_name =>Any accurate table.
Examples
[c]
sql> select * from employee;
+--------+--------+---------+
| emp_id | ename | dept_no |
+--------+--------+---------+
| 1001 | jack | 10 |
| 1002 | maddi | 20 |
| 1003 | maddie | 10 |
| 1004 | max | 20 |
| 1004 | capi | 30 |
+--------+--------+---------+
5 rows in set (0.00 sec)
sql> select * from department;
+---------+--------------+----------+
| dept_no | dept_name | city |
+---------+--------------+----------+
| 10 | finance | texas |
| 20 | capital | new york |
| 30 | manager | ausralia |
| 40 | applications | usa |
| 50 | technology | canada |
+---------+--------------+----------+
5 rows in set (0.00 sec)
sql> select employee.emp_id,employee.ename,department.dept_name,department.city from department right join employee on department.dept_no=employee.dept_no;
+--------+--------+-----------+----------+
| emp_id | ename | dept_name | city |
+--------+--------+-----------+----------+
| 1001 | jack | finance | texas |
| 1003 | maddie | finance | texas |
| 1002 | maddi | capital | new york |
| 1004 | max | capital | new york |
| 1004 | capi | manager | ausralia |
+--------+--------+-----------+----------+
5 rows in set (0.00 sec)[/c]
In the above example, the condition is optional for the table department. And is compulsory for the table employee.
Description
The condition is optional for the table on left size and is compulsory for the table on right side.
Conceptual
figure
Syntax
SELECT *FROM <table1> left [ OUTER ] JOIN table2 ON table1.column_name=table2.column_name;
Table_name =>Any accurate table.
Examples
By viewing the below example, the concept of left outer join can be understood easily.
[c]
sql> select * from employee;
+--------+--------+---------+
| emp_id | ename | dept_no |
+--------+--------+---------+
| 1001 | jack | 10 |
| 1002 | maddi | 20 |
| 1003 | maddie | 10 |
| 1004 | max | 20 |
| 1004 | capi | 30 |
+--------+--------+---------+
5 rows in set (0.00 sec)
sql> select * from department;
+---------+-----------+----------+
| dept_no | dept_name | city |
+---------+-----------+----------+
| 10 | finance | texas |
| 20 | capital | new york |
+---------+-----------+----------+
2 rows in set (0.00 sec)
sql>select emp_id,ename,dept_name,city from employee left join department on employee.dept_no=department.dept_no;
+--------+--------+-----------+----------+
| emp_id | ename | dept_name | city |
+--------+--------+-----------+----------+
| 1001 | jack | finance | texas |
| 1003 | maddie | finance | texas |
| 1002 | maddi | capital | new york |
| 1004 | max | capital | new york |
| 1004 | capi | NULL | NULL |
+--------+--------+-----------+----------+
5 rows in set (0.00 sec)
[/c]
In the above example, the condition is optional for the table employee. And is compulsory for the table department.
Key Points
- SQL Outer Joins - Gives back all rows from both the tables which satisfy the join condition
- Left Outer join - The condition is optional for the table on left side. And is compulsory for the table right side.
- Right Outer join - The condition is optional for the table on right side. And is compulsory for the table left side.