Unicon compatible 제약조건
목적
종류
EX)
SELECT *
FROM table1
UNION | DISTINCT | ALL | INTERSECT |EXCEPT
SELECT *
FROM table1
EX)
SELECT employeeId, jobTitle, officeCode
FROM employees
WHERE officeCode = '1'
UNION
SELECT employeeId, jobTitle, officeCode
FROM employees
WHERE officeCode = '3'
ORDER BY 이름
WHERE officeCode IN('1', '3')
WHERE officeCode = '1' OR officeCode = '3'
UNICON ALL : 중복 제거 X
UNICON DISTINCT : 중복 제거, 기본값, 생략 가능
Unicon compatible
SELECT 'P' 구분코드 productLine 상품라인, RIYBD(AVG(buyPrice), 2) 평균구매단가
FROM products
GROUP BY productLine
UNION
SElECT 'V' 구분코드, vendor 제조사, ROUND(AVG(buyPrice), 2) 평균구매단가
FROM products
GROUP BY vendor
ORDER BY 1;
EX) ERROR, MySQL에서는 INTERSECT 지원하지 않음
SELECT employeeId, jobTitle, officeCode
FROM employees
WHERE officeCode = '1'
INTERSECT
SELECT employeeId, jobTitle, officeCode
FROM employees
WHERE jobTitle LIKE '%Sales Manager%'
ORDER BY 이름
WHERE officeCode IN('1', '3')
WHERE officeCode = '1' OR officeCode = '3'
SELECT employeeId, jobTitle, officeCode
FROM employees
WHERE officeCode = '1' AND employeeId
IN (
SELECT employeeId
FROM employees
WHERE jobTitle LIKE '%Sales Manager%')
ORDER BY 이름;
join 연산은 시간이 매우 많이 드는 연산이다
R을 드라이빙 테이블로 할때
for r in R: # PK
for s in S: # FK
if r.col == s.col:
print r + s
S를 드라이빙 테이블로 할때
for s in S: # FK
for r in R: # PK, PK는 어차피 하나일 것이기에 더 열거해도 같은 값은 없음
if r.col == s.col:
print r + s
break
다양한 조인의 실행 순서
INNER JOIN
NATURAL JOIN
OUTER JOIN
CROSS JOIN
EX) WHERE 절 조인
SEELCT *
FROM A, B
WHERE A.a = B.b
EX) FROM 절 조인
SELECT A.a, A.b
FROM A JOIN B
ON A.officeCode = B.officeCode
EX) USING 절 조인
SELECT *
FROM A JOIN B USING(officeCode)