30. 서브쿼리(=하위쿼리)

--[1] 샘플테이블생성

Create Table dbo.Members

(

       Num Int Identity(1,1) Not Null Primary Key, -- 일련번호

       Name VarChar(25) Not Null,                  -- 이름

       Age TinyInt Null,                           -- 나이

       Address VarChar(100) Null                   -- 주소

)

Go

 

 

--[2] 예시데이터입력

Insert Into Members(Name, Age, Address)

Values('홍길동', 21, '서울')

Go

Insert Members Values('백두산', 100, '부산')

Go

Insert Members Values('한라산', 30, '부산')

Go

 

 

--[3] 데이터조회(출력)

Select * From Members

Go

 

 

--[!] 하위쿼리(서브쿼리)

-- 3번인데이터의나이보다큰데이터만출력

       --[a] 3번레코드의나이

Select Age From Members Where Num =3

 

       --[b] 3번레코드의나이보다큰데이터출력

Select * From Members Where Age > 30

 

       --[a] 3번레코드의나이

Declare @intAge Int

Select @intAge = Age From Members Where Num = 3

       --[b] 3번레코드의나이보다큰데이터출력

Select * From Members Where Age > @intAge

       --[!] 최종: 서브쿼리는기본적으로스칼라값(단일값)이반환되어져야함

Select * From Members

Where Age > (Select Age From Members Where Num = 3)

Go

 

-- 나이가평균이하인사람출력

Select * From Members Where Age <= (Select Avg(Age) From Members)

Go

 

-- 주소가'부산'인사람의나이보다크거나같은데이터출력

Select * From Members

Where Age >= (Select Age From Members Where Address = '부산')

Go -- 에러

 

-- Scalar 값이아닌다중레코드값은In 구문사용

Select * From Members

Where Age In (Select Age From Members Where Address = '부산')

Go

 

-- 위구문을개선: Or(Any, Some)연산, And(All)연산

Select * From Members

Where

       Age >= Any(Select Age From Members Where Address = '부산')

Go -- 30, 100

 

Select * From Members

Where

       Age >= All(Select Age From Members Where Address = '부산')

Go -- 100

 

 

--[6] 테이블삭제

Drop Table dbo.Members

Go