SQL문 실습 - INNER JOIN(1)

- SQL문 실습 - INNER JOIN(1) -

이제부터 INNER JOIN 문제를 풀어보면서 INNER JOIN을 어떻게 사용하는지에 대해서 설명해보겠습니다.

1.

--  Select Orders, Cusomters table
--  Join use CustomerID column
--  Select Column :
--    Customers.Country, Customers.CustomerID, 
--    Orders.OrderID, Orders.OrderDate, Orders.Freight
------------------------------------------------------------------------------
SELECT C.Country
    ,C.CustomerID
    ,O.OrderID
    ,O.OrderDate
    ,CONVERT(NUMERIC(12, 4), O.Freight) Freight
FROM Orders O 
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.OrderDate >= '1996-07-01' AND O.OrderDate < '1996-08-01'

- FROM Orders O INNER JOIN Customers C

      ON O.CustomerID = C.CustomerID

-> INNER JOIN을 사용해서 Orders테이블과 Customers테이블의 CustomerID컬럼을 기본 키로 

 연결했습니다.

- 결과 -

2.

--  Select Orders, Employees table
--  Join use EmployeeID column
--  Select Column :
--    Employees.Title, Employees.LastName, Employees.FirstName
--    Orders.OrderID, Orders.OrderDate, Orders.Freight
------------------------------------------------------------------------------
SELECT E.Title
    ,E.LastName
    ,O.OrderID
    ,O.OrderDate
    ,O.Freight
FROM Orders O 
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE O.OrderDate >= '1996-07-01' AND O.OrderDate < '1996-08-01'

- FROM Order O INNER JOIN Employees E

     ON O.EmployeeID = E.EmployeeID

-> INNER JOIN을 사용해서 Orders테이블과 Employees테이블의 Employee컬럼을 기본 키로 연결했다.

- 결과

3.

--  Select Orders, Employess table
--  Join use EmployeeID column
--  Select Column :
--    Employees.Title, Employees.LastName, Employees.FirstName,
--    Orders.OrderID, Orders.OrderDate, Orders.Freight
--  Select Only Orders.CustomerID = 'ALFKI'
------------------------------------------------------------------------------
SELECT E.Title
    ,E.LastName
    ,E.FirstName
    ,O.OrderID
    ,O.OrderDate
    ,O.Freight
FROM Orders O 
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE O.CustomerID = 'ALFKI'

- FROM Orders O INNER JOIN Employees E

      ON O.EmployeeID = E.EmployeeID

-> INNER JOIN을 사용해서 Orders테이블과 Employees테이블의 EmployeeID컬럼을 기본 키로 연결했다.

- 결과

4.

--  Select Customers, Orders, Employees table
--  Join between Customers and Orders, use CustomerID column
--  Join between Orders and Employees, use EmployeeID column
--  Select Column :
--    Customers.Country, Customers.CustomerID
--    Orders.OrderDate, Orders.Freight
--    Employees.Title, Employees.LastName
--  Select only Customers.County = 'Brazil'
------------------------------------------------------------------------------
SELECT C.Country
    ,C.CustomerID
    ,O.OrderDate
    ,O.Freight
    ,E.Title
    ,E.LastName
FROM Customers C 
INNER JOIN Orders O ON C.CustomerID = O.CustomerID 
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE C.Country = 'Brazil'

- FROM Customers C INNER JOIN Orders O

      ON C.CustomerID = O.CustomerID

INNER JOIN Employees E

      ON O.EmployeeID = E.EmployeeID

-> 위의 문장처럼 3개의 테이블도 연결할 수가 있다.

- 결과 -

여기까지 INNER JOIN에 대한 문제를 풀어봤습니다.

다음 글에서는 OUTER JOIN에 대해서 알아보겠습니다.

다음 글 : http://crazykim2.tistory.com/255

참고 : SQL Server 기본 SQL문 교육