inner join Vs outer join

[kri shan]

What is the difference between inner join and outer join ?

[James Swan]

- inner joins only returns rows that "match" in both tables
- outer joins return "all" rows from 1st table, and null valued columns in rows that don't exist in the 2nd table

for example, imagine we have this data, also for each query, we join them on column "id":

table1
 
id  | desc
----------
1   | item1
2   | item2
3   | item3
4   | item4
 

table2
 
id  | price
-----------
1   | 1.5
2   | 2.0
3   | 1.0
 

- performing an inner join on these tables (eg. by column "id") would return the following
 
id  | desc  | price
-------------------
1   | item1 | 1.5
2   | item2 | 2.0
3   | item3 | 1.0
 
- ie. it will only return rows that are in both tables
 

- performing an outer join on these table (eg. by column "id") would return the following
 
id  | desc  | price
-------------------
1   | item1 | 1.5
2   | item2 | 2.0
3   | item3 | 1.0
4   | item4 | null
 
- ie. it will return all rows from the "base" table and use "null" values in
 the columns for rows that don't exist in the 2nd table

Also to note, the SQL syntax for outer joins is database vendor specific, Oracle used different notation from say SQL Server.

[kri shan]

In Oracle outer join is defined as * symbol. in Sybase outer join is defined as + symbol. What is the outer join symbol for DB2 ?

[Jeanne Boyarsky]

Kri,

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.a=table2.b WHERE ...