SQL Joins

You Are Here

Foreign Key

Database tables have references to other tables.

user

blog_post

User Id

Foreign Key

user

blog_post

user_id

CREATE TABLE user (
    user_id bigserial primary key,
    username text NOT NULL
);

CREATE TABLE blog_post (
    blog_post_id bigserial primary key,
    user_id bigserial references user(user_id),
    post_text text
);

• One table "points" to another table using a foreign key
• In our case, each `blog_post` points to exactly one `user`

Foreign Key

user

blog_post

user_id

1; teb-a-licious

2; liz-a-tronic

3; cho-a-ramic

1; 1; "blogs rule"

2; 1; "blogs are fine"

3; 3; "I like blogging"

4; 2; "blogs are divine"

Use FKs to tell who wrote what!

Foreign Key

user

blog_post

user_id

1; teb-a-licious

2; liz-a-tronic

3; cho-a-ramic

1;  1;   "blogs rule"

2;  1;   "blogs are fine"

3;  3;   "I like blogging"

4;  2;   "blogs are divine"

CREATE TABLE user (
    user_id bigserial primary key,
    username text NOT NULL
);

CREATE TABLE blog_post (
    blog_post_id bigserial primary key,
    user_id bigserial references user(user_id),
    post_text text
);

Joins

• Joins require two tables.

• The result is a temporary table.

• This table has data from both input tables.

• Joins use an "on" clause to define the relationship between the tables.

• To join user and blog_post with the shared user_id:

select * from user 
join blog_post on user.user_id=blog_post.user_id;

Simple Join

result: "temporary table"

teb-a-licious

teb-a-licious

liz-a-tronic

cho-a-ramic

"blogs rule"

"blogs are fine"

"blogs are divine"

"I like blogging"

select 
  u.username,
  bp.post_text
from user u
join blog_post bp on u.user_id=bp.user_id;

Chain Joins

In SQL you can chain joins together as much as you'd like

user

blog_post

user_id

comment

blog_post_id

Chain Joins

Lets say we want all the comments on a blog post as well as the original author and the original blog_text.

select 
  blog_author.username as author,
  bp.post_text as post_text,
  comment.comment_text as comment_content,
from user blog_author
join blog_post bp on u.user_id=bp.user_id
join comment comment on c.blog_post_id=bp.blog_post_id;

user

blog_post

user_id

comment

user_id

blog_post_id

Chain Joins

Lets attach comments to users

Chain Joins

Lets add the comment author to our join.

Notice that we're joining the user table twice now, with different aliases.

select 
  blog_author.username as author,
  bp.post_text as post_text,
  comment.comment_text as comment_content,
  commenter.username as comment_auth
from user blog_author
join blog_post bp on u.user_id=bp.user_id
join comment comment on c.blog_post_id=bp.blog_post_id
join user commenter on commenter.user_id=comment.user_id;

Join Types

Set Theory in Venn Diagrams

You Are Here

Join Types

Remember, you're always joining exactly 2 tables

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Lets use this sample dataset to explore join types

(http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)

Join Types

There are 4 main join types

Inner

Full Outer

Left

Right

Inner Join

Inner joins target records where both tables match. Inner joins do not result in nulls since they require a match by definition.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

Full Outer Join

Full Outer Joins target ALL records from BOTH tables. Nulls appear when either side has an unmatched record.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Full Outer Join+

We can target the opposite of an inner join using a where clause

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Left Outer Join

Left Outer Joins target records from the first table and then attach the data from the second table. Nulls appear when there isn't a match on the right hand table

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Left Outer Join+

If we add a where clause, we can select only results which don't have a right hand side match for our temporary table

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
where TableB.id IS null

id  name       id    name
--  ----       --    ----
2   Monkey     null  null
4   Spaghetti  null  null

Right Joins use the second table as their starting point