[SQL] JOIN

JOIN: 여러 테이블에서 동시에 데이터 가져오기 

 

Cross Join

실제 사용 많지 않음 

ex. dogs와 owners 사이의 모든 조합을 알아보고 싶을 때 

SELECT * FROM dogs CROSS JOIN owners;
-- dogs 테이블과 owners 테이블의 모든 행을 결합하여 결과를 반환
-- dogs 테이블의 각 행은 owners 테이블의 모든 행과 한 번씩 결합

 

 

 

Inner Join 

가장 많이 사용 

어떤 row 끼리 연결시킬지 선택할 수 있음 

ex. 각 dog가 실제 owner, breed와 연결 

      owner, breed가 없는 강아지는 제외하고 반환  

SELECT dogs.name AS dog_name, owners.name AS owner_name, breeds.name AS breed_name
	FROM dogs 
  INNER JOIN owners ON dogs.owner_id = owners.owner_id
  INNER JOIN breeds ON dogs.breed_id = breeds.breed_id;
-- dogs 테이블과 owners 테이블을 owner_id가 일치하는 행끼리 결합
-- 위 결과와 breeds 테이블을 breed_id가 일치하는 행끼리 다시 결합
-- 세 테이블 모두에서 일치하는 owner_id와 breed_id를 가진 행들만 결과에 포함

SELECT dogs.name AS dog_name, owners.name AS owner_name, breeds.name AS breed_name
	FROM dogs 
  INNER JOIN owners USING (owner_id)
  INNER JOIN breeds USING (breed_id);
-- 칼럼명 구조가 같으면 위 처럼 할 수 있다 
-- owner_id / breed_id

 

 

 

Outer Join

Left Outer Join: owner가 없는 강아지를 포함 모든 강아지를 반환 

SELECT dogs.name AS dog_name, owners.name AS owner_name 
  FROM dogs 
  LEFT JOIN owners ON dogs.owner_id = owners.owner_id;

 

Right Outer Join: dog이 없는 owner를 포함 모든 owner를 반환 

SELECT dogs.name AS dog_name, owners.name AS owner_name 
  FROM dogs 
  RIGHT JOIN owners ON dogs.owner_id = owners.owner_id;

참고) SQLite에서는 Right Outer Join이 불가 

 

---

Practice

-- all dogs with their breed names
SELECT dogs.name AS dog_name, breeds.name AS breed_name
  FROM dogs
  LEFT JOIN breeds ON dogs.breed_id = breeds.breed_id;


-- all owners and their dogs 
SELECT owners.name AS owner_name, dogs.name AS dog_name
  FROM owners
  LEFT JOIN dogs ON owners.owner_id = dogs.owner_id;


-- all breeds and the dogs of that breed 
SELECT breeds.name AS breed_name, dogs.name AS dog_name
  FROM breeds
  LEFT JOIN dogs ON breeds.breed_id = dogs.breed_id;


-- all dogs with their pet passport info and owner data 
SELECT dogs.name AS dog_name, 
       pet_passports.blood_type AS blood_type, 
       pet_passports.allergies AS allergies, 
       pet_passports.last_checkup_date AS checkup,
       owners.name AS owner_name
  FROM dogs
  LEFT JOIN pet_passports ON dogs.dog_id = pet_passports.dog_id
  LEFT JOIN owners ON dogs.owner_id = owners.owner_id;
  

-- all tricks and the dogs that know them 
SELECT tricks.name AS trick_name, dogs.name AS dog_name, dog_tricks.proficiency, dog_tricks.date_learned
  FROM tricks
  LEFT JOIN dog_tricks ON tricks.trick_id = dog_tricks.trick_id
  LEFT JOIN dogs ON dog_tricks.dog_id = dogs.dog_id; 


-- all dogs that don't know a single trick
SELECT dogs.name, dog_tricks.dog_id
  FROM dogs
  LEFT JOIN dog_tricks using (dog_id)
  WHERE dog_tricks.dog_id IS NULL;

-- all breeds and the count of dogs for each breed
SELECT breeds.name, count(*) AS num_of_dogs
  FROM breeds
  RIGHT JOIN dogs ON dogs.breed_id = breeds.breed_id  -- breeds: 왼쪽 / dogs: 오른쪽
  GROUP BY breeds.name
  ORDER BY num_of_dogs DESC;


-- all owners with the count of their dogs, the average dog weight and the average dog age 
SELECT owners.name AS owner_name, 
  COUNT(dogs.dog_id), AVG(dogs.weight) AS dog_weight, AVG(2025-YEAR(dogs.date_of_birth)) AS dog_age
  FROM owners
  LEFT JOIN dogs ON owners.owner_id = dogs.owner_id  -- JOIN 구문은 GROUP BY 앞에!
  GROUP BY owners.owner_id; 
-- 나이계산: AVG(TIMESTAMPDIFF(YEAR, dogs.date_of_birth, CURDATE()))이 더 정확하다



-- all tricks and the number of dogs that know each trick ordered by poplularity
SELECT tricks.name, COUNT(dogs.dog_id) AS num_of_dogs
  FROM tricks
  LEFT JOIN dog_tricks ON tricks.trick_id = dog_tricks.trick_id
  LEFT JOIN	dogs ON dog_tricks.dog_id = dogs.dog_id
  GROUP BY tricks.name
  ORDER BY num_of_dogs DESC;

SELECT tricks.name, COUNT(*) AS num_of_dogs
  FROM tricks
  LEFT JOIN dog_tricks USING (trick_id)
  GROUP BY tricks.trick_id
  ORDER BY num_of_dogs DESC; -- 좀 더 간단함 



-- all dogs along with the count of tricks they know
SELECT dogs.name, COUNT(dog_tricks.trick_id) AS tricks_known
  FROM dogs 
  LEFT JOIN dog_tricks USING (dog_id)
  GROUP BY dogs.name
  ORDER BY tricks_known DESC;
  
  

-- all owners with their dogs and the tricks their dogs know 
SELECT owners.name AS owner_name, GROUP_CONCAT(DISTINCT dogs.name SEPARATOR ', ') AS dog_name, GROUP_CONCAT(DISTINCT tricks.name SEPARATOR ', ') AS trick
  FROM owners
  LEFT JOIN dogs USING (owner_id)
  LEFT JOIN dog_tricks ON dogs.dog_id = dog_tricks.dog_id
  LEFT JOIN tricks ON dog_tricks.trick_id = tricks.trick_id
  GROUP BY owners.name;
  
SELECT o.name, d.name, dt.proficiency, t.name
  FROM owners o
  JOIN dogs d USING (owner_id)
  JOIN dog_tricks dt USING (dog_id)
  JOIN tricks t USING (trick_id);

-- all breeds with their average dog weight and typical lifespan
SELECT breeds.name, breeds.typical_lifespan, AVG(dogs.weight) AS dog_weight
  FROM breeds
  LEFT JOIN dogs USING (breed_id)
  GROUP BY breeds.breed_id;



-- all dogs with their latest checkup date and the time since their last checkup
SELECT dogs.name, pet_passports.last_checkup_date, TIMESTAMPDIFF(DAY, pet_passports.last_checkup_date, CURDATE()) AS days_since_last_checkup
  FROM dogs
  LEFT JOIN pet_passports USING (dog_id);
  
                                          

-- all breeds with the name of the heaviest dog of that breed(서브쿼리)
SELECT breeds.name, MAX(dogs.weight) AS max_weight, dogs.name 
  FROM breeds
  JOIN dogs USING (breed_id)
  GROUP BY breeds.breed_id;
-- 에러 

SELECT b.name AS breed_name,
       d.name AS dog_name,
       d.weight AS max_weight
  FROM breeds b
  JOIN dogs d USING (breed_id)
  JOIN (
      SELECT breed_id, MAX(weight) AS max_weight
      FROM dogs
      GROUP BY breed_id   -- breed_id별 max_weight 
  ) md ON d.breed_id = md.breed_id AND d.weight = md.max_weight;

SELECT breeds.breed_id, breeds.name, dogs.weight AS heaviest_weight
  FROM breeds
  JOIN dogs USING (breed_id)
  WHERE dogs.weight = (SELECT MAX(dogs.weight) FROM dogs WHERE dogs.breed_id=breeds.breed_id);
  

-- all tricks with the name of the dog who learned it most recently 
SELECT tricks.name, dogs.name AS latest_learned_dog, MIN(TIMESTAMPDIFF(DAY, dog_tricks.date_learned, CURDATE())) AS days_after_learning
  FROM tricks
  JOIN dog_tricks USING (trick_id)
  JOIN dogs ON dog_tricks.trick_id=dogs.dog_id 
  GROUP BY tricks.trick_id;
-- 잘못: 이렇게 하면 dogs.name이 임의로 선택됨

SELECT t.name, d.name, dt.date_learned
  FROM tricks t
  JOIN dog_tricks dt USING (trick_id)
  JOIN dogs d ON dt.dog_id = d.dog_id
  WHERE dt.date_learned = (SELECT MAX(dt2.date_learned) FROM dog_tricks dt2 WHERE dt2.trick_id=dt.trick_id);