[PostgresDB-2] 12. 테이블 조인 (INNER JOIN 실습 with instagrma)

테이블 및 데이터 생성

(참고, https://cloudstudying.kr/lectures/503)

-- users 테이블
CREATE TABLE users (
  id       SERIAL       PRIMARY KEY,
  nickname VARCHAR(50),
  email    VARCHAR(100)
);
-- photos 테이블
CREATE TABLE photos (
  id       SERIAL        PRIMARY KEY,
  filename VARCHAR(255),
  user_id  INTEGER       REFERENCES users(id)
);
-- comments 테이블
CREATE TABLE comments (
  id       SERIAL         PRIMARY KEY,
  body     VARCHAR(1000),
  user_id  INTEGER        REFERENCES users(id),
  photo_id INTEGER        REFERENCES photos(id)
);
-- settings 테이블
CREATE TABLE settings (
  id            SERIAL       PRIMARY KEY,
  private       BOOLEAN,
  adding_photos VARCHAR(15),
  user_id       INTEGER      UNIQUE REFERENCES users(id)
);
-- likes 테이블 생성(사용자와 사진을 다대다 연결)
CREATE TABLE likes (
  id       SERIAL  PRIMARY KEY,
  user_id  INTEGER REFERENCES users(id),
  photo_id INTEGER REFERENCES photos(id)
);

-- users 레코드
INSERT INTO
  users(nickname, email)
VALUES
  ('cloudstudying_kr', 'mail@cloudstudying.kr'),
  ('hongpark_cs',      'sehongpark@cloudstudying.kr'),
  ('haesamq',          'haesamq@naver.com')
;
-- photos 레코드
INSERT INTO
  photos(filename, user_id)
VALUES
  ('cat-on-road.jpg',           1),
  ('sunset-over-the-ocean.jpg', 1),
  ('andromeda-galaxy.jpg',      1),
  ('white-tiger.jpg',           2),
  ('nero-the-black-cat.jpg',    2),
  ('deleted-image.jpg',         NULL),
  ('blocked-image.jpg',         NULL)
;
-- comments 레코드
INSERT INTO
  comments(body, user_id, photo_id)
VALUES
  ('meow',             1, 1),
  ('nyaong',           2, 1),
  ('냐옹',              3, 1),
  ('sunset',           1, 2),
  ('falling slowly',   2, 2),
  ('Andromeda galaxy', 1, 3),
  ('mysteriouse..!',   3, 3),
  ('wow..!',           3, NULL),
  ('oh..!',            3, NULL)
;
-- settings 레코드
INSERT INTO
  settings(private, adding_photos, user_id)
VALUES
  (FALSE, 'MANUAL', 1),
  (FALSE, 'AUTO',   2),
  (TRUE,  'AUTO',   3)
;
-- likes 레코드
INSERT INTO
  likes(user_id, photo_id)
VALUES
  (1,    1),
  (2,    1),
  (1,    2),
  (2,    2),
  (3,    2),
  (1,    3),
  (3,    3),
  (NULL, 6),
  (NULL, 7)
;

A. “사진#1”에 달린 모든 “댓글 내용”과 작성자 “닉네임”을 조회하시오.

 -- A. “사진#1”에 달린 모든 “댓글 내용”과 작성자 “닉네임”을 조회하시오.
SELECT
	comments.body,
	users.nickname
FROM
 	comments
JOIN users ON
	comments.user_id = users.id
WHERE
	comments.photo_id = 1
;

결과

B. “사진#5”의 “파일명”과 사진 게시자 “닉네임”을 조회하시오.

-- B. “사진#5”의 “파일명”과 사진 게시자 “닉네임”을 조회하시오.
SELECT
	users.nickname,
	photos.filename
FROM
	photos
JOIN users ON
	photos.user_id = users.id
WHERE
	photos.id = 5
;

결과

C. “사진#2”에 좋아요를 누른 사용자 “닉네임”과 대상 사진의 “파일명”

※ likes 테이블과 users 테이블, photos 테이블 3가지 테이블이 모두 필요 (즉, JOIN을 두번 사용)

-- C. “사진#2”에 좋아요를 누른 사용자 “닉네임”과 대상 사진의 “파일명”
SELECT
	users.nickname,
	photos.filename
FROM
	likes
JOIN users ON
	users.id = likes.user_id
JOIN photos ON
	photos.id = likes.photo_id
WHERE
	photos.id = 2
;

결과

출처 : 홍팍의 'SQL 데이터 분석, 활용!'