SQL Joins

You Are Here

Objectives

• Define "Foreign Key"
• Define "Join" as it relates to databases
• Write join queries in SQL
• Distinguish between different join types

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"

Foreign Key relationships are "One to Many"

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 
  user.username,
  blog_post.post_text
from user
join blog_post on user.user_id=blog_post.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 
  user.username as author,
  blog_post.post_text as post_text,
  comment.comment_text as comment_content,
from user
join blog_post on user.user_id=blog_post.user_id
join comment on comment.blog_post_id=blog_post.blog_post_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

Recap!

• Define "Foreign Key"
• Define "Join" as it relates to databases
• Write join queries in SQL
• Distinguish between different join types

Questions?