SQL - SPLessons

SQL Outer Joins

Chapter 38

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Outer Joins

SQL Outer Joins

shape 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

shape Description

The condition is optional for the table on right side, where as it is compulsory on the table on right side.

shape Conceptual
figure

shape Syntax


SELECT * FROM table1 Right [ OUTER ] JOIN table2 ON table1.column_name=table2.column_name;

Table_name =>Any accurate table.

shape Examples

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)

In the above example, the condition is optional for the table department. And is compulsory for the table employee.

Left outer join

shape Description

The condition is optional for the table on left size and is compulsory for the table on right side.

shape Conceptual
figure

shape Syntax

SELECT *FROM <table1> left [ OUTER ] JOIN table2 ON table1.column_name=table2.column_name;

Table_name =>Any accurate table.

shape Examples

By viewing the below example, the concept of left outer join can be understood easily.

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)

In the above example, the condition is optional for the table employee. And is compulsory for the table department.

Summary

shape 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.