SQL 3주차

SQL 3주차

INTERSECT

여러개의 질의 결과에서 공통으로 존재하는 레코드만 선택 
예시로 Customer와 Orders 테이블이 있다고 가정하면

SELECT customer_name FROM customer
INTERSECT
SELECT customer_name FROM orders WHERE region = 'Seoul';

customer 테이블에서 customer_name 컬럼의 값을 선택하고 orders에서 region이 'Seoul'인 레코드들의 customer_name을 선택하여 두 질의의 결과에서 공통되는 고객 이름을 반환한다
쉽게 말하면 중첩질의간 교집합 결과를 보여주는 것이다
 

UNION

여러개의 SELECT 문의 결과 집합을 합쳐 중복을 제거하고 하나의 결과 집합으로 반환한다.
예시로 Customer와 Orders 테이블이 있다고 가정하면 

SELECT customer_name FROM customer
UNION
SELECT customer_name FROM orders;

customer 테이블의 customer_name과 orders의 customer_name의 고객 이름을 반환하는데 이 때 중복되는 값은 제거한다. 쉽게 말하면 중첩질의간 중복을 제외한 합집합 결과를 보여주는 것이다.
 

UNION ALL

일반 UNION과 비슷하지만 의도적으로 중복을 허용할 경우 UNION ALL을 사용한다.
모든 결과를 그대로 합쳐서 반환하는 질의어이다.
당연히 UNION보다 상대적으로 빠르게 처리되며 연산자는 결과 집합의 컬럼 수와 데이터 타입은 select문에서 선택한 첫번째 질의의 결과와 같아야 한다.
예시로 Customer와 Orders 테이블이 있다고 가정하면

SELECT customer_name FROM customer
UNION ALL
SELECT customer_name FROM orders;

먼저 customer 테이블의 customer_name을 선택하고 orders의 customer_name을 선택하여 중복을 포함하여 모든 결과를 보여준다.
 
 

EXCEPT

Oracle에선 MINUS로도 쓰이는 EXCEPT는 Q1-Q2와 같다 즉 Q1-(Q1∩Q2)의 결과이다.
select문의 결과 집합의 컬럼 수와 데이터 타입은 모두 일치해야한다.
 
 
 

Chapter 2~3 Nested Query

 

중첩 쿼리문

Nested Query는 SQL에서 하나 이상의 서브 쿼리를 포함하는 쿼리이다.
서브 쿼리는 select from where 절 어디든 사용 가능하며 DB에서 특정한 조건을 만족하는 데이터를 검색하는데 사용
중첩된 순서로 처리되기 때문에 서브 쿼리가 먼저 실행 -> 결과 기반으로 메인 쿼리 실행.

SELECT SUM(quantity)
FROM order_details
WHERE order_id IN (
  SELECT order_id
  FROM orders
  WHERE customer_id = 123
);

예시로 고객 아이디가 123인 주문번호의 판매액 총합을 출력하는 쿼리문이다.
 
IN 외에도 ALL, ANY, EXISTS 등이 있다.
 
모든 SQL 프로그램에서 사용되는 명령어는 아니라는 점 참고할 것
'Gizmo-Works' 만든 제품들보다 비싼 제품들이 무엇인지 확인하는 쿼리이다.

select name
from Product
where price > ALL (
	select price
    from Product
    where maker = 'Gizmo-Works');
    
    
    -- ALL은 서브쿼리의 모든 결과 비교

• ANY : 서브쿼리의 결과 중 한 줄의 결과를 비교(하나라도 비교 조건 충족시 참을 반환)
• ALL : 서브쿼리의 모든 결과를 비교(모두 비교후 충족시 참을 반환)
• 부등호는 수학의 부등호와 같은 역할

중첩쿼리에서 ANY와 ALL은 비교연산자와 함께 사용되는 연산자며 외부 쿼리에서 사용할 수 있게 서브 쿼리의 결과를 줄이는데 사용된다.
 

• EXISTS : 존재 여부를 확인하는 연산자
  • 반환 결과 존재시 TRUE, 존재하지 않는 경우 FALSE 반환

select p1.name
from Product p1
where p1.maker = 'Gizmo-Works'
	AND EXISTS(
    	SELECT p2.name
        from Product p2
        where p2.maker <> 'Gizmo-Works'
        	AND p1.name = p2.name)
            
            -- <>는 !=와 같은 의미
            
p2메이커와 'Gizmo-Works'와 다른 결과를 가지고 있는 로우들을 찾고 p1과 p2의 이름이 같은(제품의 이름이 같다는 것)
것 중 같은 이름을 가지고 있는 Product의 이름을 찾는 쿼리문

 

Correlated Queries

• 상관 관계가 있는 쿼리

<Schema>
Movie(title, year, director, length)

select DISTINCT title
from Movie AS m
where year <> ANY (
	select year
    from Movie
    where title = m.title)
    
    안쪽에 있는 영화 제목과 바깥에 있는 영화 제목이 같은지 비교한 후 그들의 출시연도가 언제인지
    출력을한 후 같은 연도가 아닌 영화의 제목들을 출력(중복 제거)

 
 

Complex Correlated Query

• 복잡한 연관 쿼리는 서브 쿼리와 외부 쿼리가 서로 연결되어 있는 복잡한 쿼리이다.
• 이는 한 쿼리의 결과에 따라 다른 쿼리의결과가 변경되므로 서브 쿼리가 외부 쿼리와 연관되어 있는 것으로 알려져있다.
• 이는 두개 이상 테이블간의 연관성을 확인하는데 사용됨 

SELECT *
FROM orders
WHERE EXISTS (
  SELECT *
  FROM customers
  WHERE customers.customer_id = orders.customer_id
  AND customers.country = 'USA'
);

고객 테이블의 고객 아이디와 주문 테이블의 고객 아이디가 같고
고객의 나라가 USA인 고객 정보만 필터링하여 반환한다

복잡한 연관 쿼리는 대개 일반적인 연관 쿼리보다 더 높은 성능 비용이 발생할 수 있으며 최적화하기 어렵다
따라 적절한 인덱스를 설정하거나 다른 DB 설계방법을 고려해야할 수 있다.
 

Chapter 4 Aggregation(집합)

 

SUM, MIN, MAX, AVG

select AVG(price)
from Product
where maker = 'Toyota'
와 같이 사용

단 COUNT의 경우

사진과 같이 사용한다.
 
COUNT : 중복을 상관하지 않고 모든 행을 센다
중복 제거가 필요할 경우
select COUNT(DISTINCT bookname) 과 같이 사용
 
 

<Schema>
Purchase(product, date, price, quantity)

select SUM(price*quantity)
from Purchase
where product = 'bagel';

-- 베이글의 총 판매액을 구하는 질의

이와 같이 attribute간 연산을 하여 그 총합을 구하는 질의도 가능하다

 

GROUP BY &  HAVING

집계함수로 특정 컬럼을 대상으로 데이터를 그룹화하여 그룹 단위로 데이터를 집계하는 기능이다.

다음과 같은 테이블이 있다고 가정하자.

SELECT 고객명, SUM(가격) 
FROM 주문데이터 
GROUP BY 고객명;

-- 주문 데이터에서 고객명으로 그룹화하여 고객명과 그 고객들의 물품 가격들의 총합을 표시한다

다음과 같이 고객명을 기준으로 고객의 구매한 가격들을 모두 더한 질의 결과가 나오는 것을 확인할 수 있다.
 
여기서 그룹화할 때 조건을 추가하는 역할을 하는 HAVING 절이 있다.

SELECT 고객명, SUM(가격) 
FROM 주문데이터
GROUP BY 고객명 
HAVING SUM(가격) >= 600;

이 질의문에 대한 결과는

이다.
쿼리문을 자세히 살펴보자
WHERE과 HAVING 모두 특정 조건을 지정하기 위한 절이다.
이 둘의 차이점은 WHERE의 경우 그룹화와 상관없이 모든 투플(row)를 대상으로 조건을 지정하는 것이며
HAVING의 경우 그룹화를 한 이후에  조건을 지정하는 것이다.
 
HAVING 절은 반드시 GROUP BY 절과 함께 작성해야하고 where절보다 뒤에 나와야하며 검색 조건에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야 한다.
또한 GROUP BY를 사용할 때 주의해야할 부분은 SELECT 절에 GROUP BY에 포함되어 있지 않은 컬럼을 사용하는 경우 오래가 발생할 수 있다. 이유는 GROUP BY에 지정된 컬럼 외에 다른 컬럼의 데이터가 그룹화되어 출력되지 않기 때문이다. 이 때문에 GROUP BY 사용시에는 SELECT 절에 그룹화할 컬럼과 집계함수를 사용하는 것이 좋다.
 
이에 대한 예시로 

SELECT 고객명, 상품명, SUM(가격) 
FROM 주문데이터 
GROUP BY 고객명;

이 쿼리문에서 GROUP BY에 포함되어 있는 고객명 컬럼과 집계함수인 SUM(가격)을 할 경우 사용 가능하지만 이에 포함되지 않는 상품명을 입력할 경우

Column '주문데이터.상품명' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

다음과 같이 GROUP BY에 포함되어 있지 않은 컬럼이라고 뜬다.
주의해서 사용하도록 하자.
 

데이터베이스에서의 쿼리문 실행 순서

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

먼저 데이터를 선택하고
JOIN을 통해 합치며
WHERE문을 이용해 조건을 걸고
GROUP BY로 그룹화 한 후
HAVING으로 그룹화된 데이터에서 또 조건을 부여한다
그 후 투플들을 선택하고
정렬한다

 
 

Group by Nested Query

<Schema>
Author(login(기본키), name)
Wrote(login, url)

select DISTINCT Author.name
from Author
where COUNT(
	select Wrote.url
    from Wrote
    Where Author.login = Wrote.login) > 10
    
- 10개 이상을 저장한 저자들을 찾아보자

1. 작성한 사용자가 누군지를 찾아야함
2. Author.login = Wrote.login 로그인이 서로 같은 경우에 대해서 url들을 출력하고
3. 해당 URL을 10개 이상 가지고 있는 작가의 이름을 출력

 
위의 쿼리문을 GROUP BY를 이용한다면?

select Author.name
from Author, Wrote
where AUthor.login = Wrote.login
GROUP BY Author.name
HAVING COUNT(Wrote.url) > 10

1. Author, Wrote 데이터 선택 -- from
2. 두 데이터의 로그인 정보가 같은 경우 -- where
3. 작가 이름을 그룹화하여 -- GROUP BY
4. 해당 작가의 URL이 10개 이상인 경우의 --HAVING
5. 작가의 이름을 출력한다 -- select

 
이 두 구문은 결과는 같으나 차이점이 존재한다.

• 1번의 경우 SFW 쿼리가 2번 등장함
• 2번의 경우 SFWGH로 한번만 등장
• 고로 2번의 경우(GROUP BY)가 훨씬 효율적이라고 말할 수 있다.

 
 

Chapter 5 Join

Quantifiers

조인 조건에서 두 테이블간의 관계를 표현하는 방법 중 하나
 

1. Existential Quantifier: ∃ (이 기호는 'there exists' 또는 '존재한다'라는 의미를 나타냅니다.)
2. Universal Quantifier: ∀ (이 기호는 'for all' 또는 '모든'이라는 의미를 나타냅니다.)

 

• EXISTS
  • 하나의 테이블이 다른 테이블과 관계가 있는지 체크
  • 서브쿼리 내에서 사용되며 서브 쿼리에서 반환되는 데이터가 적어도 하나 이상 존재하는지 체크
  • 보통 외래키 제약조건을 확인할 때 사용
• NOT EXISTS
  • EXISTS와 반대로 작동되는데, 역시 서브 쿼리내에서 사용되며 서브 쿼리 내에서 반환되는 데이터가 없을 경우 TRUE 반환
• ALL
  • 하나의 값을 다른 값과 비교할 때 사용된다. 모든 값이 비교 조건을 충족하면 TRUE 반환
• ANY/SOME
  • 하나의 값을 다른 값과 비교할 때 사용
  • 하나 이상의 값이 비교 조건을 충족하면 TRUE를 반환
  • 보통 ANY와 SOME을 서로 대체 가능하나 SOME의 경우 표준 SQL에서 정의된 방식
• IN
  • IN연산자는 서브 쿼리에서 반환된 결과 집합에 값이 포함되어 있는지 체크
  • 보통 where절에서 사용됨

NULL

값이 없다 - (X)
값이 정해지지 않았다 - (O)
 
쿼리문에서 조건을 걸 경우 NULL 값은 출력되지 않는다
예를 들어

select *
from Person
where age < 25 or age >=25
		OR age IS NULL 
        
        
        -- OR age IS NULL까지 해줘야 NULL값인 age가 출력된다
        
        x is NULL
        x is NOT NULL

 

Inner Joins

<Schema>
Product(name, category)
Purchase(prodName, stor)

select Product.name, Purchase.stor
from Product
	JOIN Purchase ON Product.name = Purchase.prodName
    
    혹은 
    
select Product.name, Purchase.store
from Product, Purchase
where Product.name = Purchase.prodName

Inner join과 NULL 값을 만날경우 무시하고 출력하지 않는다
Inner Joins + NULLS = Lost data
이를 방지하기 위한 조인 방식이 있다.
 

Outer Joins

NULL 값이 있더라도 포함시키는 조인

• Left Outer Join : 테이블 왼쪽을 기준으로 오른쪽에 NULL이 있다고 하더라도 포함시키는 조인
• Right Outer Join : 테이블 오른쪽을 기준으로 왼쪽에 NULL이 있다고 하더라도 포함시키는 조인
• Full Outer Join : 양쪽에 모두 NULL이 있어도 상관하지 않고 출력시키는 조인