Outer Joins

Joins that include all rows, regardless of whether there is a matching row, are called outer joins.

select * 
from titles, titleauthor
where titles.title_id *= titleauthor.title_id

SAP supports both Transact-SQL and ANSI outer joins. Transact-SQL outer joins use the *= command to indicate a left outer join and the =* command to indicate a right outer join. Transact-SQL outer joins were created by SAP as part of the Transact-SQL language.

select * 
from titles left join titleauthor
on titles.title_id = titleauthor.title_id

• Inner and Outer Tables
  The terms outer table and inner table describe the placement of tables in an outer join.
• Outer Join Restrictions
  If a table is an inner member of an outer join, it cannot participate in both an outer join clause and a regular join clause.
• Views Used with Outer Joins
  If you define a view with an outer join, then query the view with a qualification on a column from the inner table of the outer join, the results may not be what you expect. The query returns all rows from the inner table.
• ANSI Inner and Outer Joins
  Use ANSI join syntax to write either inner joins or outer joins, or to join views.
• ANSI outer joins
  Joins that produce a joined table that includes all rows from the outer table, regardless of whether or not the on clause produces matching rows, are called outer joins.
• Transact-SQL Outer Joins
  Transact-SQL includes syntax for both left and right outer joins. The left outer join, *=, selects from the first table all rows that meet the statement’s restrictions. The second table generates values if there is a match on the join condition. Otherwise, the second table generates null values.