SQL Database(DML) - join

1. inner join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

aliases 사용 가능

SELECT column_name(s)
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.column_name = t2.column_name;

예제

SELECT O.OrderID, C.CustomerName
FROM Orders as O
INNER JOIN Customers  as C ON O.CustomerID = C.CustomerID;

3개의 테이블

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

 

2. left join

left에 있는 거 + 둘다 있는거

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

CustomerName이 left이고 orderID가 NULL -> Customers에만 존재하는 ID가 합쳐진 것

 

3. right join 

left에서 방향만 바뀐 것

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

 

4. full join

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

양쪽 테이블에 NULL 발생 가능

left -> right table column에 NULL 생김

rigth -> left table column에 NULL 생김

 

5. self join

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

https://moonpiechoi.tistory.com/124

[SQL] 셀프 조인(SELF JOIN)

 

 

 

JOIN

https://pearlluck.tistory.com/46

DB JOIN 정리(INNER/LEFT/RIGHT/OUTER)