[SQL] SQL Query 쿼리문 (3) - CASE, SubQuery(feat. IN, EXISTS)

 

 

 

 

 

CASE, SUBQUERY( IN/ NOT IN, EXISTS/ NOT EXISTS )

 

 

 

 

 

SQLite tutorial chinook SQLite sample 데이터를 사용했습니다. 

 

https://www.sqlitetutorial.net/sqlite-sample-database/

SQLite Sample Database And Its Diagram (in PDF format)

 

 

 

 

 

 

 

CASE

 

 

 

SQL에서 if문의 역할을 하는 것은 CASE 입니다. 

CASE 를 사용하게 되면 조건을 설정하고 그에 따라 다르게 정해진 결과를 받을 수 있습니다.

 

 

- EmployeeId와 함께 Title이 IT로 시작하면 IT TEAM, Sales로 시작하면 Sales TEAM, 나머지는 Others로 하여 불러오기

SELECT EmployeeId , 
	CASE 
		WHEN e.Title LIKE 'IT%' THEN 'IT TEAM'
		WHEN e.Title LIKE 'Sales%' THEN 'Sales TEAM'
		ELSE 'Others'
	END
FROM employees e

 

 

 

 

 

 

 

 

SUBQUERY

 

 

 

서브 쿼리(SUBQUERY)는 쿼리문을 작성할 때 쿼리문을 포함하는 것을 말합니다. 

 

 

 

서브쿼리는, 

SELECT 절에 쓰이는 스칼라 서브쿼리 (Scalar Subquery)

FROM 절에 쓰이는 인라인 뷰 (Inline View)

WHERE 절에 쓰이는 다중 서브쿼리가 있습니다.

 

 

 

1. SELECT 절에 쓰이는 서브쿼리 

 

 

 

SELECT 절에 서브쿼리를 사용할 때의 주의점은

서브쿼리를 통해 조회되는 값이 하나의 레코드이거나 하나의 값이어야 한다는 점입니다. 

아래에서 해본 것을 예로 설명해보면 invoice당 한 CustomerId가 연결되어 있고,

CustomerId당 하나의 Phone 정보만 등록이 되어있기 때문에 오류없이 쿼리 결과를 조회할 수 있습니다. 

하지만 반대로 Phone정보별 invoiceId를 가져오려고하면 invoicesr가 여러개지만 하나만 출력되게 됩니다. 

 

 

- invoiceId별로 고객의 Phone정보 가져오기 

SELECT invoiceId, (SELECT c.Phone FROM customers c WHERE c.CustomerId  = i.CustomerId) AS 'Phone'
FROM invoices i

 

 

 

Phone에 따라 InvoiceID를 서브쿼리로 가져오면 첫 레코드의 결과만 가져오는 것을 알 수 있습니다. 

SELECT Phone, (SELECT i.InvoiceId FROM invoices i WHERE i.CustomerId  = c.CustomerId) AS 'InvoiceID'
FROM customers c

 

 

 

위 출력 결과에서 Phone에 해당하는 InvoiceID는 98 하나만 나왔지만, 

JOIN하여 살펴보면 7개의 레코드를 가지고 있음을 알 수 있습니다. 

SELECT c.Phone, i.InvoiceID
FROM customers c 
jOIN invoices i ON i.CustomerId  = c.CustomerId
WHERE c.Phone = '+55 (12) 3923-5555'

 

 

 

2.  FROM 절에 쓰이는 서브쿼리 

 

 

 

서브 쿼리는 FROM에서도 사용할 수 있는데, 해당 경우는 서브 쿼리를 통해 조회된 결과를 테이블로 사용할 수 있습니다. 

 

 

- invoices 테이블에서 Total이 10이 넘는 레코드를 가져와 BillingCountry별 CustomerId수 가져오기 

SELECT BillingCountry, COUNT(CustomerId)
FROM (
	SELECT CustomerId, BillingCountry
	FROM invoices 
	WHERE Total > 10
    )
GROUP BY BillingCountry

 

 

 

3. WHERE 절에 쓰이는 서브쿼리 

 

 

• IN, NOT IN

 

IN은 서브 쿼리 안에 특정 값이 들어있는지 확인할 수 있습니다. 

서브 쿼리 결과로 조회된 것에 있는 것들만 True로 가져오게 됩니다. 

 

 

- Total이 10이 넘는 InvoiceId, CustomerId, InvoiceDate, Total 가져오기 ( 최종 조회된 결과의 Total은 당연히 10이 넘음 )

SELECT InvoiceId, CustomerId, InvoiceDate, Total
FROM invoices
WHERE Total IN (SELECT Total FROM invoices WHERE Total > 10)

 

 

 

- Total이 10이 넘지 않는 InvoiceId, CustomerId, InvoiceDate, Total 가져오기 ( 최종 조회된 결과의 Total은 당연히 10 이하 )

SELECT InvoiceId, CustomerId, InvoiceDate, Total
FROM invoices
WHERE Total NOT IN (SELECT Total FROM invoices WHERE Total > 10)

 

 

 

• EXISTS NOT EXISTS

 

EXISTS 혹은 NOT EXISTS 와 같은 경우에는 서브쿼리에 레코드가 존재하는지를 확인합니다.

만약에 조회된 레코드가 존재한다면 참을 아닌 경우에는 거짓을 리턴합니다.

따라서 일반적으로 EXISTS를 사용하면 첫번째 결과만 출력하면 연산을 멈추기 때문에 IN 보다 빠릅니다. 

 

 

- artists 테이블의 ArtistId와  albums 테이블의 ArtistId가 일치하는 레코드 중 artists 테이블에서 Name 가져오기 

SELECT Name 
FROM artists ar
WHERE EXISTS (
	SELECT 1
	FROM albums al
	WHERE ar.ArtistId = al.ArtistId)
ORDER BY ArtistId