D19 - EX04 JOIN ( INNER JOIN , OUTER JOIN, CROSS JOIN, UNION JOIN)

-- 10/5 일
-- JOIN 2개이상의 테이블을 조회
--************************
-- INNER JOIN 구문 형식 
-- SELECT <열 목록>
--  FROM <첫 번째 테이블>
--     INNER JOIN <두 번째 테이블>
--     ON <조인될 조건>
--  [WHERE 검색조건]
--************************

-- Pubs 데이터베이스 컨텍스트로 이동

--1. titles 테이블 데이터 조회
USE pubs

SELECT *
FROM titles

--2. 위 조회에 출판사 이름을 추가해서 조회

-- 데이터베이스 다이어그램 누르면 안된다
-- 상위 마우스 오른쪽 클릭 속성
-- 파일-> 사용자 이름 추가
-- 데이터베이스 다이어그램 클릭 새다이어그램 클릭 전체 추가 클릭
-- 그러면 다이어그램을 볼수가 있다.

SELECT t.title_id ,
    t.title,
    t.type,
    t.pub_id,
    p.pub_name,
    t.price,
    t.pubdate
FROM
 titles t -- titles 을 publishers 와 연관 관계해서 보고 조건은 아래부분
 INNER JOIN publishers p  -- INNER 안쓰면 디폴트로 INNER JOIN 이다
 ON t.pub_id = p.pub_id   -- <조건> 을 쓴다
 

 

-- Northwind 데이터베이스 컨텍스트로 이동
USE Northwind
--3. 제품테이블의 productid, productname, quantityperunit, unitprice
--   공급자이름( companyname), 분류명(categoryname) 을 조회하세요

SELECT * FROM Products

SELECT p.ProductID,
    p.productname,
    p.quantityperunit,
    p.unitprice,
    s.companyname,
    c.categoryname   
FROM Products p
     INNER JOIN Suppliers s
     ON p.SupplierID = s.SupplierID
     INNER JOIN Categories c
     ON p.CategoryID = c.CategoryID
    
---------------------------------------------------------------- 
--  OUTER JOIN (외부 조인)
--    개념
--    내부조인은 조인의 조건인 만족되지 않는 행은 출력되지 않지만,
--    외부조인은 조건이 만족되지 않는 행까지 출력됨.
--
-- pubs 데이터베이스 컨텍스트로 이동
USE pubs

--5. 출판사별 출판현황(출판사명, 도서정보)
SELECT
  p.pub_name,
  t.*
FROM publishers p
  --INNER JOIN titles t
  LEFT OUTER JOIN titles t --LEFT는 정보 (왼쪽)titles가 NULL 이여도 출력
  ON p.pub_id = t.pub_id
ORDER BY  
     p.pub_name
    
-- Northwind 데이터베이스 컨텍스트로 이동
USE Northwind

--6. 모든 고객의 아이디, 이름, 주문정보 조회
SELECT * FROM Orders    

SELECT
  c.CompanyName,
  o.*
FROM    Customers c
  LEFT OUTER JOIN Orders o
  ON o.CustomerID = c.CustomerID
ORDER BY
        c.CompanyName 
       
       
--7. 모든 카테고리의 제품 정보 조회(카테고리 이름 + 제품정보)       

SELECT
     p.*,
     c.CategoryName
    
FROM    Categories c
  LEFT OUTER JOIN Products p
  ON p.ProductID = c.CategoryID
ORDER BY
        c.CategoryName

--8. 직원 + 제품   -- CROSS JOIN e 에 대해 p 와 관계한 모든 출력
SELECT
  e.EmployeeID,
  p.ProductName
FROM
  Employees e
  CROSS JOIN Products p
 
 
SELECT * FROM Employees
--9. 직원 테이블에서
--  아이디,
--   이름(firstname + lastname),
--  상위결재자dlfma름(firstname + lastname) 조회

--  같은 테이블 SELF JOIN (자체조인)
--  자기자신과 자기자신이 조인한다는 의미.

SELECT
  e1.EmployeeID,
  e2.FirstName + ' ' + e2.LastName [name],
  e1.FirstName + ' ' + e1.LastName [manager]
FROM
  Employees e1
  INNER JOIN Employees e2
  ON e1.EmployeeID = e2.ReportsTO
 
SELECT * FROM Employees
 

--    UNION JOIN
--    UNION 은 두 쿼리의 결과를 행으로 합치는 것.(단, 중복 제거)
--    UNION ALL 은 중복까지 허용함
--    EXCEPT는 두 번째 쿼리에 해당하는 것을 제외함.

--9. 두 테이블의 데이터 조합 (컴럼 개수, 자료형 일치)

SELECT
   FirstName, HireDate
FROM Employees
UNION ALL
SELECT
   CustomerID, orderDate
FROM Orders