VontineDev

Outer Joins

• Return all rows from one table and any matching rows from second table
• One table’s rows are “preserved”
  • –Designated with LEFT, RIGHT, FULL keyword
  • –All rows from preserved table output to result set
• Matches from other table retrieved
• Additional rows added to results for non-matched rows
  • –NULLs added in places where attributes do not match
• Example: Return all employees and for those who have taken orders, return the order amount. Employees without matching orders will display NULL for order amount.

DEMO

Customer 테이블은 FirstName, LastName은 모든 행을 포함하고,

SalesOrderHeader 테이블의 SalesOrderNumber는

CustomerID로 매칭되는 경우 표기되고, 매칭되지않는 경우 NULL로 표기된다.

--Get all customers, with sales orders for those who've bought anything
SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
ORDER BY c.CustomerID;

847개의 결과가 나타남

위의 쿼리에서 WHERE절을 아래처럼 추가하면,

한번도 구매하지 않은 Customer를 표기할 수 있다.

--Return only customers who haven't purchased anything
SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
WHERE oh.SalesOrderNumber IS NULL 
ORDER BY c.CustomerID;

815개의 결과가 나타남

Product 테이블에서 Name과 SalesOrderNumber를 표기한다.

SalesOrderNumber가 없는 경우 NULL로 표기

Product 테이블과 SalesOrderHeader는 매칭가능한 key가 없으므로

Product LEFT JOIN SalesOrderDetail LEFT JOIN SalesOrderHeader순으로 조합한다.

--More than 2 tables
SELECT p.Name As ProductName, oh.SalesOrderNumber
FROM SalesLT.Product AS p
LEFT JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
LEFT JOIN SalesLT.SalesOrderHeader AS oh --Additional tables added to the right must also use a left join
ON od.SalesOrderID = oh.SalesOrderID
ORDER BY p.ProductID;

위의 결과에서 Category를 추가하고 싶다면 Product 테이블과 ProductCategory를 INNER JOIN한다.

두 쿼리 모두 잘 동작하나 INNER JOIN을 앞에다 두는 편이 이해하기 편하다.

SELECT p.Name As ProductName, c.Name AS Category, oh.SalesOrderNumber
FROM SalesLT.Product AS p
LEFT OUTER JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON od.SalesOrderID = oh.SalesOrderID
INNER JOIN SalesLT.ProductCategory AS c --Added to the left, so can use inner join
ON p.ProductCategoryID = c.ProductCategoryID
ORDER BY p.ProductID;

SELECT p.Name As ProductName, c.Name AS Category, oh.SalesOrderNumber
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS c --Added to the left, so can use inner join
ON p.ProductCategoryID = c.ProductCategoryID
LEFT OUTER JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON od.SalesOrderID = oh.SalesOrderID
ORDER BY p.ProductID;

Inner Joins

• Return only rows where a match is found in both input tables
• Match rows based on attributes supplied in predicate
• If join predicate operator is =, also known as equi-join

INNER 키워드는 쓰지 않아도 JOIN을  Inner Join으로 인식하지만 어떤 Join인지 쉽게 식별하도록 명시적으로 붙이면 좋다.

DEMO

--Basic inner join
SELECT SalesLT.Product.Name As ProductName, SalesLT.ProductCategory.Name AS Category
FROM SalesLT.Product
INNER JOIN SalesLT.ProductCategory
ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID;

테이블에 Alias(별명)을 사용할 수 있다.

-- Table aliases
SELECT p.Name As ProductName, c.Name AS Category
FROM SalesLT.Product AS p
JOIN SalesLT.ProductCategory As c
ON p.ProductCategoryID = c.ProductCategoryID;

2개 이상의 테이블을 Join할 수 있다.

-- Joining more than 2 tables
SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name As ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
JOIN SalesLT.Product AS p
ON od.ProductID = p.ProductID
ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID;

-- Multiple join predicates
SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name As ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
JOIN SalesLT.Product AS p
ON od.ProductID = p.ProductID AND od.UnitPrice = p.ListPrice --Note multiple predicates
ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID; 

마지막에서 한줄 위의 od.UnitPrice < p.ListPrice 를 바꾸면

-- Multiple join predicates
SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name As ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
JOIN SalesLT.Product AS p
ON od.ProductID = p.ProductID AND od.UnitPrice < p.ListPrice --Note multiple predicates
ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID; 

JOIN Concepts

• Combine rows from multiple tables by specifying matching criteria
  • –Usually based on primary key – foreign key relationships
• It helps to think of the tables as sets in a Venn diagram

JOIN Syntax

FROM 절에서 JOIN 

SELECT ...
FROM   Table1 JOIN Table2
       ON <on_predicate>;

FROM 절에서 콤마(,)를 사용해 JOIN

Cartesian products(모든 경우의 수를 구하는 방법)이 이루어지므로 일반적으로 이 방법은 추천하지 않는다.

SELECT ...
FROM   Table1, Table2
WHERE  <where_predicate>;