1. SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
※ Different Types of SQL JOINs
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Ex.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders(join의 기준/주체가 되는 테이블명)
INNER JOIN Customers(합칠/서브가 되는 테이블명) ON Orders.CustomerID=Customers.CustomerID;
2. SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
" A에는 데이터가 있으나 B에 데이터가 없으면 데이터를 보여주지마라!!"
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
EX.
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
※ JOIN Three Tables
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);
3. SQL LEFT (OUTER) JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
EX.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
CustomerName | OrderID |
---|---|
Alfreds Futterkiste | null |
Ana Trujillo Emparedados y helados | 10308 |
Antonio Moreno Taquería | 10365 |
Around the Horn | 10355 |
Around the Horn | 10383 |
B's Beverages | 10289 |
Berglunds snabbköp | 10278 |
Berglunds snabbköp | 10280 |
Berglunds snabbköp | 10384 |
Blauer See Delikatessen | null |
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders). -> The reason why there are null values.
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
OrderID | LastName | FirstName |
---|---|---|
West | Adam | |
10248 | Buchanan | Steven |
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
EX.
The following SQL statement matches customers that are from the same city:
SELECT A.CustomerName AS CustomerName1, B.CustomerName ASCustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
【기타】 개발자의 MSSQL팁! INNER JOIN & LEFT OUTER JOIN
https://blog.naver.com/gmrdud2gh?Redirect=Log&logNo=221255060472
'Data Analysis > SQL' 카테고리의 다른 글
데이터베이스 튜닝 (1) | 2019.03.05 |
---|---|
MS SQL nchar vs nvarchar (0) | 2019.01.29 |
[w3schools] SQL Study② (0) | 2019.01.25 |
[w3schools] SQL Study① (0) | 2019.01.22 |