SQL Outer Join

OUTER JOIN

The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only.

Visual Presentation of SQL Outer Join

The subtypes of SQL OUTER JOIN

• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN

Syntax:

Select * 
FROM table1, table2 
WHERE conditions [+];

Example:

Here is an example of outer join in SQL between two tables.

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+

+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

To get company name and company id columns from company table and company id, item name, item unit columns from foods table, after an OUTER JOINING with these mentioned tables, the following SQL statement can be used:

SQL Code:

SELECT company.company_name, -- Selecting the company name from the 'company' table.
       company.company_id, -- Selecting the company ID from the 'company' table.
       foods.company_id, -- Selecting the company ID from the 'foods' table.
       foods.item_name, -- Selecting the item name from the 'foods' table.
       foods.item_unit -- Selecting the item unit from the 'foods' table.
FROM company, foods -- Specifying the tables to select data from, 'company' and 'foods', using comma syntax.
WHERE company.company_id = foods.company_id(+);
-- Specifying the condition for selecting rows, where the company ID in the 'company' table matches the company ID in the 'foods' table, using an outer join syntax.

Explanation:

• The SQL code retrieves specific columns from the 'company' and 'foods' tables, joining them based on the company ID.


• The query selects the 'company_name' and 'company_id' columns from the 'company' table and the 'company_id', 'item_name', and 'item_unit' columns from the 'foods' table.


• It performs a cross join between the 'company' and 'foods' tables using comma syntax, effectively combining every row from 'company' with every row from 'foods'.


• The 'WHERE' clause specifies the condition for selecting rows where the company ID in the 'company' table matches the company ID in the 'foods' table, using an outer join syntax


• with '(+)' to indicate the outer join condition. This retrieves all rows from 'company' and only matching rows from 'foods', with null values for non-matching rows.

Output:

COMPANY_NAME    COMPANY_ID COMPANY_ID ITEM_NAME       ITEM_UNIT
--------------- ---------- ---------- --------------- ----------
Akas Foods      16         16         Chex Mix        Pcs
Jack Hill Ltd   15         15         Cheez-It        Pcs
Jack Hill Ltd   15         15         BN Biscuit      Pcs
Foodies.        17         17         Mighty Munch    Pcs
Jack Hill Ltd   15         15         Pot Rice        Pcs
Order All       18         18         Jaffa Cakes     Pcs
sip-n-Bite.     19

Key points to remember

Click on the following to get the slides presentation -

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL CROSS JOIN
Next: SQL LEFT JOIN