SQL 중급(2)

INNER JOIN

양쪽 테이블에 데이터가 다 있을 때 합친다.
SELECT *
FROM 테이블1, 테이블2
WHERE 테이블1.Id = 테이블2.userId

• ERD : 테이블 간 컬럼 이름이 다를 때. 고급반
• INNER JOIN 제외 다 OUTER JOIN

LEFT JOIN

SELECT *
FROM 사용자테이블
LEFT JOIN 주문테이블 ON 사용자테이블.Id=주문테이블.userId
한 번도 주문 안한 고객 정보도 보고 싶을 때는 WHERE 주문테이블ID IS NULL 등으로 입력 가능
보통은 LEFT JOIN을 많이 사용한다.
LEFT OUTER JOIN 과 LEFT JOIN은 동일하게 작동

해커랭크 문제

• African Cities
  city에도 name이 있고, country에도 name이 있어서 select할 때도 명시해주기.
  SELECT city.name
  FROM city
  INNER JOIN country ON city.countrcode = country.code
  WHERE country.continent = "Africa"

• Asian Population(Population Cencus)
  SELECT SUM(city.population)
  FROM city
  INNER JOIN country ON city.countrycode = country.code
  WHERE country.continent = "Asia"

• Average Population of Each Continent
  SELECT country.continent
  , FLOOR(AVG(city.population))
  FROM city
  INNER JOIN country ON city.countrycode = country.code
  GROUP BY country.continent

리트코드 문제

• 183. Customers Who Never Order
       SELECT c.Name AS Customers
       FROM customers AS c
       LEFT JOIN orders AS o ON c.id=o.customerid
       WHERE o.id IS NULL
       WHERE절 전에 먼저 출력해서 Output을 일부 보고 정리해보고 비교하면
       NULL값이 나오는 데이터가 있다.

• Self JOIN

리트코드 문제

181. EmployeesEarning More Than
     SELECT Employee.Name AS employee_name
     , Employee.Salary AS employee_salary
     , Manager.Name AS manager_name
     , Manager.Salary AS manager_salary
     FROM employee
     INNER JOIN employee as Manager ON Employee.managerid = Manager.id
     WHERE Employee.Salary > Manager.Salary

---

SELECT Employee.Name AS Employee
FROM employee
INNER JOIN employee as Manager ON Employee.managerid = Manager.id
WHERE Employee.Salary > Manager.Salary

---

197. Rising Temperature
     SELECT today.id AS today_id
     , today.recordDate AS today_recordDate
     , today.Temperature AS today_temperature
     , yesterday.id AS yesterday_id
     , yesterday.recordDate AS yesterday_recordDate
     , yesterday.Temperature AS yesterday_temperature
     FROM Weather AS today
     INNER JOIN Weather AS yesterday ON yesterday.id + 1 = today.id
     WHERE today.temperature > yesterday.temperature

---

SELECT today.id AS
FROM Weather AS today
INNER JOIN Weather AS yesterday ON yesterday.id + 1 = today.id
WHERE today.temperature > yesterday.temperature

---

SELECT today.id
FROM Weather AS today
INNER JOIN Weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature

• 시간 더하기, 빼기
• DATE_ADD(기준날짜, INTERVAL)
  SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND)
  MINUTE, HOUR, DAY, MONTH, YEAR, -1 YEAR
• DATE_SUB(기준날짜, INTERVAL)
  SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND)

---

• UNION, UNION ALL
• 집합연산 : 위아래로 붙인다
• DISTINCT 가 디폴트값
• 다 보고 싶으면 UNION ALL
• FULL OUTER JOIN이 MySQL에서 구현하고 싶다면, LEFT JOIN과 RIGHT JOIN을 UNION으로 합친다

---

• 해커랭크 문제
• Symmetric Pairs
  1) 20 20일 경우
  SELECT X, Y
  FROM functions
  WHERE X=Y
  GROUP BY X, Y
  HAVING COUNT(*)=2

UNION

2) 20 21
2) 22 23
SELECT f1.X, f1.Y, f2.X, f2.Y
FROM functions AS f1

INNER JOIN functions AS f2 ON f1.X=f2.Y AND f1.Y=f2.X

---

SELECT f1.X, f1.Y
FROM functions AS f1
INNER JOIN functions AS f2 ON f1.X=f2.Y AND f1.Y=f2.X
WHERE f1.X < f1.Y
ORDER BY X

유니온 위에 쿼리에는 ORDER BY를 쓸 수 없다.