Joining Tables in SQL

Linklist

1. Outer Joins

Syntax in the tablelist (Informix), SQL-command FROM:

[OUTER] Tablename [ Alias ] [,...]

Description

There are some situations, where you need additionally to the information of one table some information from an other table, but dont want, by joining the tables, loose the records of the first table, wich do not have corresponding records on the second table. These joins of tables are called "outer joins".

Example 1 (Informix):

SELECT a.vnum, a.vname, b.kname
FROM tverkauf a, OUTER tkunden b
WHERE a.vnum = b.vnum;

Select all salesman with their customers, but select salesman without customers too.

Example 2 (Standard-SQL):

SELECT a.vnum, a.vname, b.kname
FROM tverkauf a, tkunden b
WHERE a.vnum = b.vnum
UNION
SELECT vnum, vname, '____________'
FROM tverkauf
WHERE vnum NOT IN
( SELECT vnum FROM tkunden );

Select all salesman with their customers, but select salesman without customers too.

Example 3 (some other RDBMS):

SELECT a.vnum, a.vname, b.kname
FROM tverkauf a, tkunden b
WHERE a.vnum(+) = b.vnum;

Select all salesman with their customers, but select salesman without customers too.