w3resource logo


SQL OPUTER JOIN

SQL Outer Join

<<PreviousNext>>

Seondary Nav

Description

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.

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.

Sample table : foods

Sample table : company

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 :

SELECT company.company_name,company.company_id,
foods.company_id,foods.item_name,foods.item_unit 
FROM company, foods 
WHERE company.company_id = foods.company_id(+);

Explanation

This SQL statement would return all rows from the company table and only those rows from the foods table where the joined fields are equal.

The (+) after the foods.company_id field indicates that, if a company_id value in the company table does not exist in the foods table, all fields in the foods table will be displayed as NULL in the result set.

Pictorial Presentation of Sql outer join

pictorial representation of Sql outer join

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 -

SQL JOINS, slide 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.



<<PreviousNext>>

 

Looking for some other tutorial?