Joins

JOIN_ME.md

reference : https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

The tables

#\d people
                                Table "public.people"
 Column |         Type          |                      Modifiers                      
--------+-----------------------+-----------------------------------------------------
 name   | character varying(20) | 
 id     | integer               | not null default nextval('people_id_seq'::regclass)
Indexes:
    "people_pkey" PRIMARY KEY, btree (id)

#\d cats
                                  Table "public.cats"
  Column   |         Type          |                     Modifiers                     
-----------+-----------------------+---------------------------------------------------
 name      | character varying(20) | 
 id        | integer               | not null default nextval('cats_id_seq'::regclass)
 person_id | integer               | 
Indexes:
    "cats_pkey" PRIMARY KEY, btree (id)

The DATA

# select * from people;
 name | id 
------+----
 zulh |  1
 dave |  2
 zaim |  3
(3 rows)

# select * from cats;
       name       | id | person_id 
------------------+----+-----------
 storen           |  1 |         3
 tiddles          |  2 |         1
 Mr. Bigglesworth |  3 |         1
 furry funball    |  4 |        72
(4 rows)

INNER join

(same as where people.id=cats.person_id)

# select * from people inner join cats on people.id=cats.person_id;
 name | id |       name       | id | person_id 
------+----+------------------+----+-----------
 zaim |  3 | storen           |  1 |         3
 zulh |  1 | tiddles          |  2 |         1
 zulh |  1 | Mr. Bigglesworth |  3 |         1
(3 rows)

FULL outer join

# select * from people full outer join cats on people.id=cats.person_id;
 name | id |       name       | id | person_id 
------+----+------------------+----+-----------
 zaim |  3 | storen           |  1 |         3
 zulh |  1 | tiddles          |  2 |         1
 zulh |  1 | Mr. Bigglesworth |  3 |         1
      |    | furry funball    |  4 |        72
 dave |  2 |                  |    |          
(5 rows)

LEFT outer join

# select * from people left outer join cats on people.id=cats.person_id;
 name | id |       name       | id | person_id 
------+----+------------------+----+-----------
 zaim |  3 | storen           |  1 |         3
 zulh |  1 | tiddles          |  2 |         1
 zulh |  1 | Mr. Bigglesworth |  3 |         1
 dave |  2 |                  |    |          
(4 rows)

RIGHT outer join

# select * from people right outer join cats on people.id=cats.person_id;
 name | id |       name       | id | person_id 
------+----+------------------+----+-----------
 zaim |  3 | storen           |  1 |         3
 zulh |  1 | tiddles          |  2 |         1
 zulh |  1 | Mr. Bigglesworth |  3 |         1
      |    | furry funball    |  4 |        72
(4 rows)

LEFT outer join with WHERE clause

people who are NOT owned by cats

# select * from people left outer join cats on people.id=cats.person_id where cats.person_id is null;
 name | id | name | id | person_id 
------+----+------+----+-----------
 dave |  2 |      |    |          
(1 row)

people who are owned by cats (shows use of distinct to de-duplicate result set)

# select distinct people.name,cats.person_id from people inner join cats on people.id=cats.person_id;
name | person_id 
------+-----------
 zulh |         1
 zaim |         3
(2 rows)