MySQL_테이블의 결합 JOIN

INNER JOIN

INNER JOIN : 기준 테이블과 조인 테이블 모두 데이터가 존재하는 것만 조회가 됨

 

 

SELECT *
FROM Orders;

SELECT *
FROM Customers;

 

CustomerID로 INNER JOIN을 한다면?

SELECT * 
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

여러 개의 TABLE을  INNER JOIN 할 수 있다.

SELECT * 
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID;

 

 

OUTER JOIN

 OUTER JOIN : 기준 테이블에만 데이터가 존재하면 조회됨

 

LEFT JOIN

 

기준 테이블인 Customers에는 존재하지만 Orders에 CustomerID가 없어도 null값으로 대체되어 조회가 된다.

SELECT * 
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

 

 

RIGHT JOIN

RIGHT JOIN 은 기준 테이블이 반대가 된다는 것을 제외하면 LEFT JOIN과 동일하다.

 

 

---

HackerRank:  Average Population of Each Continent

 

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

 

SELECT Country.continent
     , FLOOR(AVG(City.population))
FROM City
    INNER JOIN Country ON City.countrycode = Country.code
GROUP BY Country.continent;

 

---

LeetCode: Customers Who Never Order 

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

The query result format is in the following example.

 

SELECT c.name AS Customers
FROM Customers AS c
    LEFT JOIN Orders AS o ON c.id = o.customerId
WHERE o.customerId IS NULL;