Link to home
Create AccountLog in
Avatar of bowemc
bowemc

asked on

Inner and Outer joins

Hi,

My weakest skill in SYBASE is probably joins. I'm well able to use equi joins but always struggle with inner and outer joins. When I need them I research them and inevitably have forgotten which does what etc by the time I need it again!

Can anyone explain how they work and where / when they should be used, Alternatively if someone knows a site where it is very well and elegantly covered I'd very much appreciate their help also. I'm not simply looking for the first result returned by google though! Thanks
Avatar of pollock_d
pollock_d

Whenever I need refreshing on SQL Joins, I go to w3 schools...try out:

http://www.w3schools.com/sql/sql_join.asp
ASKER CERTIFIED SOLUTION
Avatar of grant300
grant300

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The best way to do this is by example.  See below:

Ben Slade
Chevy Chase, MD

> create table customers ( name varchar(10), order_id int )
> select * from customers;
 name       order_id   
 ---------- -----------
 Joe                  1
 Bill                 2
 Mike                 3
 
> create table orders( order_id int, product varchar(10) null, state char(2) null )
> select * from orders;
 order_id    product    state
 ----------- ---------- -----
           1 beer       MD   
           2 NULL       MD   
           3 wine       VA   
 
-- Old syntax
--
> select a.name, b.product
> from customers a, orders b
> where a.order_id *= b.order_id
 name       product   
 ---------- ----------
 Joe        beer      
 Bill       NULL      
 Mike       wine      
 
--New syntax
--
> select a.name, b.product
> from customers a
> left outer join orders b on a.order_id=b.order_id
 name       product   
 ---------- ----------
 Joe        beer      
 Bill       NULL      
 Mike       wine      
 
-- This is fine but it doesn't show the real reason for the new
-- outer join syntax.   The problem comes if you want to add another
-- non-outer join where clause.  For example:
 
-- Old syntax
-- Gives not-what-you-wanted output
--
> select a.name, b.product
> from customers a, orders b
> where a.order_id *= b.order_id
> and b.state="MD"
 name       product   
 ---------- ----------
 Joe        beer      
 Bill       NULL      
 Mike       NULL      
 
--New syntax
--
> select a.name, b.product
> from customers a
> left outer join orders b on a.order_id = b.order_id
> where b.state="MD"
 name       product   
 ---------- ----------
 Joe        beer      
 Bill       NULL      
 
 
So the "where" clause here act's like a filter *after* the
outer join.   That's the reason for the new outer join syntax

Open in new window

Ooops, the:

  and b.state="MD"

in the "old syntax" example was supposed to be

  where b.state="MD"