w3resource logo


SQL OPUTER JOIN

SQL Outer Join

rating Sql outer join has average rating 8 out of 10. Total 35 users rated.

<<PreviousNext>>

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, table2WHERE conditions [+];

Parameters

Name Description
table1, table2 Name of the tables participating in joining.
conditions 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_unitFROM company, foodsWHERE 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 representation of Sql outer join

pictorial representation of Sql outer join

Output

Sql outer join

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.

You might be interested to read the following topics :



<<PreviousNext>>