SQL 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.
Pictorial 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
Select * FROM table1, table2 WHERE conditions [+];
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(+);
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.
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.
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.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: SQL CROSS JOIN
Next: SQL LEFT JOIN
SQL: Tips of the Day
Drop all tables whose names begin with a certain string
DECLARE @cmd varchar(4000) DECLARE cmds CURSOR FOR SELECT 'drop table [' + Table_Name + ']' FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE 'prefix%' OPEN cmds WHILE 1 = 1 BEGIN FETCH cmds INTO @cmd IF @@fetch_status != 0 BREAK EXEC(@cmd) END CLOSE cmds; DEALLOCATE cmds
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises