SQL Join Clause

PLEASE NOTE: This tutorial comes from my FREE video course called SQL Boot Camp.

-- drop table posts;
-- drop table comments;

create table posts (
  id integer,
  title character varying(100),
  content text,
  published_at timestamp without time zone,
  type character varying(100)
);

insert into posts (id, title, content, published_at, type)
values (100, 'Intro to SQL', 'Epic SQL Content', '2018-01-01', 'SQL');
insert into posts (id, title, content, published_at, type)
values (101, 'Intro to PostgreSQL', 'PostgreSQL is awesome!', now(), 'PostgreSQL');
insert into posts (id, title, content, type)
values (102, 'Intro to SQL Where Clause', 'Easy as pie!', 'SQL');
insert into posts (id, title, content, type)
values (103, 'Intro to SQL Order Clause', 'What comes first?', 'SQL');
insert into posts (id, title, content, type)
values (104, 'Installing PostgreSQL', 'First things first.', 'PostgreSQL');

create table comments (
  id integer,
  post_id integer,
  user_id integer,
  submitted_at timestamp without time zone,
  comment character varying(500)
);

insert into comments (id, post_id, user_id, submitted_at, comment) values
(200, 100, 45,  '2018-01-01', 'This is awesome.'),
(201, 100, 543, '2018-01-03', 'Great job!'),
(202, 100, 99,  '2018-01-05', 'I learned some new things.'),
(203, 101, 33,  '2018-02-05', 'What does SQL mean?'),
(204, 101, 976, '2018-02-06', 'Where can I get Postgres?'),
(205, 101, 233, '2018-02-07', 'Cannot wait for the next one')
;

select * from posts;
select * from comments;

-- Inner join
select *
from posts
inner join comments on posts.id = comments.post_id;

select p.title, c.comment
from posts p
inner join comments c on p.id = c.post_id;

-- left outer join
select p.title, c.comment
from posts p
left outer join comments c on p.id = c.post_id;

-- comment count
select p.title, count(c.comment)
from posts p
inner join comments c on p.id = c.post_id
group by p.title;

select p.title, count(c.comment)
from posts p
left outer join comments c on p.id = c.post_id
group by p.title;

-- Putting it all together
select p.title, count(c.comment)
from posts p
left outer join comments c on p.id = c.post_id
where p.type = 'SQL'
group by p.title
order by p.title;

Please go ahead and leave a comment below if you have any questions about this tutorial.