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.

users

posts

User Id

Foreign Key

users

posts

user_id

CREATE TABLE users (
    id serial PRIMARY KEY,
    username varchar(255) NOT NULL
);

CREATE TABLE posts (
    id serial PRIMARY KEY,
    user_id integer REFERENCES users(id),
    content text
);

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

Foreign Key

users

posts

user_id

1; wessss

2; rob-a-dub-dub

3; h-gulch

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; wessss

2; rob-a-dub-dub

3; h-gulch

1; 1; "blogs rule"

2; 1; "blogs are fine"

3; 3; "I like blogging"

4; 2; "blogs are divine"

CREATE TABLE users (
    id serial PRIMARY KEY,
    username varchar(255) NOT NULL
);

CREATE TABLE posts (
    id serial PRIMARY KEY,
    user_id integer REFERENCES users(id),
    content 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 users
JOIN posts ON users.user_id = posts.user_id;

Simple Join

result: "temporary table"

SELECT users.username, posts.content
FROM users
JOIN posts ON users.id = posts.user_id;

wessss

wessss

rob-a-dub-dub

h-gulch

"blogs rule"

"blogs are fine"

"I like blogging"

"blogs are divine"

Chain Joins

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

users

posts

user_id

comments

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
  users.username AS author,
  posts.content AS post_text,
  comments.content AS comment_text,
FROM users
JOIN posts ON users.user_id = posts.user_id
JOIN comments ON comments.post_id = 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

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

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

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?