2021-12-03 15:46 작성

My SQL basic 메모

Table of contents

INNER JOIN 심화 예시


1. 2개 tables 연결하기

위와 같이 productLine을 foreign key(표와 표를 연결 시키는 공통 column)라고 하자.

다음과 같은 데이터를 얻고 싶다고 가정하자:

  • products에서 productCode와 productName을 가져온다.
  • productlines에서 textDescription을 가져온다.
SELECT
    productCode, 
    productName,
    textDescription
FROM 
    products
INNER JOIN productlines USING (productLine);

output:

productCode productName textDescription
S10_1949 1952 Alpine Renault 1300 Attention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office.
S10_4757 1972 Alfa Romeo GTA Attention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office.



2. 3개 tables 연결하기

INNER JOIN을 두 번 사용해 세 개의 tables를 연결할 수도 있다:

orders와 orderdetails를 orderNumber로 연결하고 orderdetails와 products를 productCode로 연결한다.

SELECT
    orderNumber,
    orderDate,
    orderLineNumber,
    productName,
    quantityOrdered,
    priceEach
FROM
    orders
INNER JOIN
    orderdetails USING (orderNumber)
INNER JOIN
    products USING (productCode)
ORDER BY
    orderNumber,
    orderLineNumber;

output:

orderNumber orderDate orderLineNumber productName quantityOrdered priceEach
10100 2003-01-06 1 1936 Mercedes Benz 500k Roadster 49 35.29
10100 2003-01-06 2 1911 Ford Town Car 50 55.09
10100 2003-01-06 3 1917 Grand Touring Sedan 30 136.00
10100 2003-01-06 4 1932 Alfa Romeo 8C2300 Spider Sport 22 75.46
10101 2003-01-09 1 1928 Mercedes-Benz SSK 26 167.06
10101 2003-01-09 2 1938 Cadillac V-16 Presidential Limousine 46 44.35
10101 2003-01-09 3 1939 Chevrolet Deluxe Coupe 45 32.53
10101 2003-01-09 4 1932 Model A Ford J-Coupe 25 108.06
10102 2003-01-10 1 1936 Mercedes-Benz 500K Special Roadster 41 43.13

…



3. 4개 tables 연결하기 

SELECT 
    orderNumber,
    orderDate,
    customerName,
    orderLineNumber,
    productName,
    quantityOrdered,
    priceEach
FROM
    orders
INNER JOIN customers 
    USING (customerNumber)
INNER JOIN orderdetails 
    USING (orderNumber)
INNER JOIN products 
    USING (productCode)
ORDER BY 
    orderNumber, 
    orderLineNumber;

output:

orderNumber orderDate customerName orderLineNumber productName quantityOrdered priceEach
10100 2003-01-06 Online Diecast Creations Co. 1 1936 Mercedes Benz 500k Roadster 49 35.29
10100 2003-01-06 Online Diecast Creations Co. 2 1911 Ford Town Car 50 55.09
10100 2003-01-06 Online Diecast Creations Co. 3 1917 Grand Touring Sedan 30 136.00
10100 2003-01-06 Online Diecast Creations Co. 4 1932 Alfa Romeo 8C2300 Spider Sport 22 75.46
10101 2003-01-09 Blauer See Auto, Co. 1 1928 Mercedes-Benz SSK 26 167.06

FROM과 첫 INNER JOIN 절이 공유하는 column이 존재하여야하고(만약 공유 column이 존재하지 않을 경우 에러 발생) 그 이하의 INNER JOIN에서는 tables 간 column의 공유순서를 고려하지 않아도 된다. 각각의 연결 column의 개별 value는 identity를 가져야만 tables를 연결해도 데이터의 고유성이 유지될 것이다.



4. 다른 연산자 사용하기

등호 연산자(=) 외에도 보다 큼 >, 보다 적음 < 그리고 같지 않음 <> 연산자를 사용할 수도 있다.

다음의 예에서는 S10_1678 코드를 가진 제품 중 제작자의 권장 소매자 가격(MSRP: manufacturer’s suggested retail price)보다 싼 제품의 가격을 보다 적음 < 연산자를 통해 찾는다.

SELECT 
    orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p
INNER JOIN orderdetails o 
   ON p.productcode = o.productcode
      AND o.priceEach < p.msrp
WHERE
    p.productcode = 'S10_1678';

output:

orderNumber productName msrp priceEach
10107 1969 Harley Davidson Ultimate Chopper 95.70 81.35
10121 1969 Harley Davidson Ultimate Chopper 95.70 86.13
10134 1969 Harley Davidson Ultimate Chopper 95.70 90.92
10145 1969 Harley Davidson Ultimate Chopper 95.70 76.56