[w3schools] SQL ③ - JOIN series

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.

4. SQL RIGHT (OUTER) JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match.

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

EX.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

OrderID	LastName	FirstName
	West	Adam
10248	Buchanan	Steven

5. SQL FULL JOIN Keyword

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;

6. SQL SELF JOIN Keyword

A self JOIN is a regular join, but the table is joined with itself.

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