SELECT
FROM
SELECT *
FROM users, orders
WHERE users_id = orders_id
users 와 orders 테이블을 합쳐서 만들 수 있는 모든 경우의 수 행에서 users_id = orders_id 조건 뽑기
SELECT *
FROM users
INNER JOIN orders ON users_id = orders_id
두 테이블을 붙이는 기준은 users_id = orders_id 이다.
- 합치고 싶은 테이블: Orders, Customers
- 기준 컬럼: Orders.CustomerID = Customers.CustomerID
SELECT city.name
FROM city
INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
WHERE country.continent = "Africa"
SELECT SUM(city.population)
FROM city
INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
WHERE country.continent = "Asia"
SELECT country.continent, FLOOR(AVG(CITY.Population))
FROM city
INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
GROUP BY country.continent
- 한번이상 주문을 한 고객 정보 뽑기
SELECT * FROM Customers INNER JOIN Orders ON Customers .CustomerID = Orders.CustomerID
- 상품 주문 건수에 상관 없이 고객 정보까지 뽑기
SELECT * FROM Customers LEFT JOIN Orders ON Customers .CustomerID = Orders.CustomerID
- 한번도 상품을 주문해보지 않은 고객 정보만 뽑기
SELECT * FROM Customers LEFT JOIN Orders ON Customers .CustomerID = Orders.CustomerID WHERE OrderID IS NULL
SELECT customers.Name AS Customers
FROM Customers AS Customers
LEFT JOIN orders ON customers.Id = orders.CustomerId
WHERE orders.id IS NULL
SELECT Employee.Name as Employee
FROM Employee
INNER JOIN Employee as Manager ON Employee.managerid = Manager.id
WHERE Employee.Salary > Manager.Salary
여기서 굳이 원래 테이블에 Manager 테이블은 따로 AS로 명명해주었지만 Employee는 AS를 쓰지 않았다.
만약 AS를 하고 싶다면 table명 수정이 필수!:SELECT e.Name as Employee FROM Employee AS e INNER JOIN Employee as Manager ON e.managerid = Manager.id WHERE e.Salary > Manager.Salary
SELECT Employee.Name as Employee
FROM Employee
WHERE employee.Salary > Manager.Salary
FROM Employee
INNER JOIN Employee as Manager
5-2. 합치는 기준을 확인한다: 원래 테이블의 managerid = Manager 테이블의 idFROM Employee
INNER JOIN Employee as Manager ON Employee.managerid = Manager.id
SELECT today.id
FROM Weather AS yesterday
INNER JOIN Weather AS today ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature