You Are Here
Database tables have references to other tables.
user
blog_post
User Id
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
);
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"
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 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;
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;
In SQL you can chain joins together as much as you'd like
user
blog_post
user_id
comment
blog_post_id
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;
author | post_text | comment_text |
---|
You Are Here
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/)
Inner
Full Outer
Left
Right
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 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
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 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
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