SQL 기초20 집합연산자 UNION, UNION ALL, INTERSECT, EXISTS, ANY

# 집합 연산자

 

#1 UNION (합집합)

*중복값은 제거

 

SELECT lname, fname FROM tbl_club_a UNION

SELECT sung, irum FROM tbl_club_b;

---

#2 UNION ALL

*중복값은 제거하지 않고 모두 보여준다.

 

예제1

SELECT lname, fname, FROM tbl_club_a UNION ALL SELECT sung, irum FROM tbl_club_b;

 

예제2

SELECT * FROM tbl_club_a WHERE no < 3 UNION SELECT * FROM tbl_club_b
WHERE no < 3 ORDER BY no DESC;

---

#3 INTERSECT

*교집합

 

SELECT lname, fname FROM tbl_club_a INTERSECT SELECT sung, irum FROM tbl_club_b;

---

#4 EXISTS

*IN과 같은 기능이다.

 

SELECT * FROM tbl_member

WHERE EXISTS (SELECT * FROM tbl_member WHERE no =5) ;

---

#5 ANY

*아무 값과 일치한다.라는 뜻

예를들어 ANY (3,4,6)라고 되어있으면 3,4,6중에서 하나라도 안에 있으면 True를 반환한다.

 

SELECT name price FROM tbl_stock

WHERE price = ANY (SELECT price FROM tbl_stock WHERE country LIKE ‘america’);