20210325
SQL Tip, SQL 팁, 1=1, 저장 프로시저, MSSQL 설치 일반
Q. Where 문에 맨 앞에 1=1을 쓰는 이유는?
SELECT (컬럼명1), ...
FROM (테이블명)
WHERE 1=1
AND (컬럼명1) = (값)프로젝트의 db query문을 보다보면, 상기와 같은 형식으로 쓰이는 sql 문을 확인할 수 있다. 1=1은 항상 참이기 때문에, 있어도 그만, 없어도 그만인 구문이지만 마치 관습처럼 쓰여져 왔다. 이유를 간단히 말하면 "주석처리"를 쉽게 하기 위함이다. 한마디로 편의성이다.
WHERE 문 다음에, 바로 조건을 붙일 경우, 이를 주석처리한다면, 다음 줄에 WHERE 문을 다시 작성해야되기 때문이다.
요즘처럼, DB Mapper가 있고, Mapper가 제공하는 Query builder 함수를 잘 사용하면, 해당하는 구문이 필요없겠다.
그러나, 이전에는 String builder 를 통해, query를 직접 작성해야 했고, WHERE 1=1 이후, 다음 줄부터 AND (컬럼명) = (값) 형식으로 추가해나가는 것이 일반적이었다.
사용시 주의해야할 점은 아래와 같다.
SELECT문의 경우 위와 같은 방식으로, 검색값이 있으냐에 따라, "AND 컬럼명 = 값" 을 유기적으로 추가해주면서 구현하면 편리하다.
DELETE, UPDATE의 WHERE 조건에 1=1을 활용할 시 주의해야하는데, 만약 1=1 조건만 남게 되면, 테이블이 전체 지워지거나, 테이블의 전체 row를 하나의 값으로 모두 덮어씌워질 수 있다는 점이다. SELECT의 경우 1=1 조건만 남게 되면, 전체 row를 출력하므로, 별로 문제가 되지 않는다.
아래는 주로 사용되는 방식이다.
StringBuffer sql = new StringBuffer();
sql.append("\n SELECT * ");
sql.append("\n FROM test_tbl ");
sql.append("\n WHERE 1=1 ");
if( first != null ){
sql.append("\n AND first = '1' ");
}
if( second != null ){
sql.append("\n AND second = '1' ");
}
Q. DBMS가 지원하는 Stored Procedure(SP, 스토어드_프로시저, 저장 프로시저) 호출 vs Query문을 직접 전송
Stored Procedure란 무엇일까? DBMS가 제공하는 기능으로써, 쿼리를 함수 형식으로, 정의해두고 필요할 때 파라미터만 넘겨서 필요한 DB작업을 수행하는 기능이다. 프로젝트를 경험하다보면, 모든 DB 쿼리를 SP 형식으로 정의하여, 화면별 C, R, U, D 4개의 SP를 작성하고, DBMS의 SP를 호출하여 처리하는 경우도 있고, 비즈니스 로직 단에서, 쿼리문을 직접 작성하여, DB에 요청하는 경우가 있다. 무엇이 권장되는 방식이고, 바람직한 것일까?
SP는 쿼리문을 마치 하나의 메서드 형식으로 만들고 어떠한 동작을 일괄적으로 처리하는 용도로 사용된다. 여러 개의 칼럼을 조회하고 여러 개의 테이블을 조인하고 거기에 조건까지 넣어줌으로써 하나의 쿼리를 만드는데 엄청나게 긴 쿼리문이 생기게 된다.
이러한 여러개의 쿼리를 사용할 때마다 긴 쿼리문을 써야 한다면 엄청나게 불편할 텐데 이를 프로시저에 저장하고, 저장된 프로시저를 호출하여 프로그래밍을 하는것이 훨씬 효율적입니다. 다만 쿼리 자체가 복잡하므로, SP 내용을 파악하는 것 또한 어려운 건 사실이다.
장점
1. Transaction 처리가 용이하다. 말그대로, SP만 호출하면 Transaction 처리를 안전하게 알아서 해준다.
- SP 없이 트랜잭션을 처리하려면, 서버에서 SqlCommand의 commit() 등의 함수를 이용해 코딩하게 되는데, 원격에서 통신하면서 락을 오래잡게 되기 때문에 서비스 사망하게 되는 현상이 발생할 수 있다.
2. 개발 업무의 구분이 가능하다. DBA가 프로젝트 내 존재하는 경우, 권장 된다.
- 상대적으로 API 웹서버가 하는 역할을 줄이고, DB를 단순히 CRUD 용도로 쓰기보다는, 복잡한 쿼리를 DBMS 상의 SP 내부에 구현하여 API처럼 구현할 경우, 웹서버는 SP를 호출하여, 결과값만 전달해주는 역할만 하게될 수 있다.(양날의 검인 기능이다.)
- 모든 쿼리에 대한 퍼포먼스 체크와 최적화가 보다 용이하다.(잘 분리했을 때의 이야기이다. 양날의 검일 수 있다.)
- DB쪽 개발자와의 협업도 상당히 편리한 편이다. DBA가 따로 존재하는 경우 SP를 도입하는 것이 낫다.
3. DB 보안이 향상된다.
- 자체적인 보안 설정 기능을 가지고 있으며, Stored program 단위로 실행 권한을 부여할 수 있다.
- SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있다.
4. 일반 SQL로는 할 수 없는 IF/WHILE문(절차적 기능)을 구현할 수 있다.
- SQL 문장에 IF / While 과 같은 제어문장 사용 가능하다. 어플리케이션 소스코드를 줄여줄 수 있다. (양날의 검인 기능이다.)
5. 일반적인 sql 쿼리로는 할 수 없었던, 기능의 추상화가 가능하다.
(ex) 여러 테이블에 걸쳐 유일한 일련번호를 발급하되, 일련번호에 자체적인 헤더 값과 시간 정보를 덧붙여서 생성하는 요건이 존재하는 경우
- 이러한 요구사항을 해결하기 위해서는 단순 Table 시퀀스(auto increment)를 사용하지 못하는데 SP를 이용하면 해결 가능하다.
6. 네트워크 소요 시간이 절감된다.
- 하나하나의 쿼리가 아주 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간이 문제가 될 수 있지만, 요즘은 별 차이는 없다.
- 쿼리할 내용에 대한 파라메터만 날리면 되니 쿼리문도 짧아지고, 네트워크 사용량도 줄어든다.
7. 복잡한 쿼리일수록 성능 향상이 존재한다.
- 쿼리 스트링을 보내면 DBMS 상에서 파싱등 추가 적인 프로세스가 있는 반면, SP의 경우 처음 컴파일할때만 좀 시간이 걸리고, 그 다음 쿼리부터는 캐쉬되어서 빠르다.
- 쿼리를 날리면 그것 처리하는것에 대한 기능 및 정보를 캐쉬에 저장해두고 비슷한 쿼리에 대해 캐쉬에 담겨 있는 기능을 바로 꺼내서 성능을 최적화 한다.
8. 기타 부가적인 장점들이 존재한다. 소스와 독립적이고, 비즈니스 로직적인 부분이 SP 내부에 쿼리, IF, WHILE문만으로도 구현 가능하기 때문이다.
- 월별 특정 작업을 db function과 stored procedure로 만들어 db job 으로 월마다 실행해서 월별 계산을 db에서 할 수 있다.
- 서버를 내렸다 올릴 필요 없이 프로시져는 그냥 수정하고 db에 반영할 수 있다.
단점
1. Application 코드의 조각화가 일어나서, 디버깅이 어려울 수 있다. 한 눈에 파악하기 너무 어렵다.
- 기존의 DB가 단순한 수준의 CRUD 역할만 했을 때보다, 더 많은 Role을 가져가게 되므로, 예전에는 소스 코드만 보고 디버깅을 하면 되었지만, 이제는 DBMS 상의 SP까지 찾아들어가서, 버그와 오류를 수정해야 한다는 단점이 존재한다.
- SP가 많아지면 코드의 양이 줄어들고, DB쪽 과 계층을 두어서 깔끔해 진다. 그러나 동시에 히든 코드가 될 수가 있다.
- 깔끔하게 분리된 경우가 아니면, 비즈니스 로직에 모두 구현되어 있는 것보다 못한 경우가 있다.
- SP가 App에 어디 사용되는 지 확인이 어렵다.
- SP를 쓰게 되면 소스같이 버전 관리 안하는경우도 있는데 이것도 해주어야 한다.
- SP는 다음 유지보수자가 유지보수하기 힘들다.
2. 문자열이나 숫자 연산 처리 성능이 떨어진다.
- 한 번에 많은 쿼리를 실행해야 할 때 효율적이지만(One Time Many Query Request), 다른 DBMS에 비해 SP 프로그램은 성능이나 최적화가 부족하여 수행 능력이 떨어진다. (오라클 pl/sql보다 2배 떨어짐)
- 문자열이나 숫자 연산을 위해서도 일반 java, c로 처리할 때보다, 매우 부족한 성능을 내기 때문에, 해당 작업은 Web 서버 상에서 처리하는 것이 나은 선택이다.
3. DB 확장이 힘든 편이다.
- 서비스 사용자가 많아져 서버수를 늘려야할 때, DB 수를 늘리는 것이 더 어려운 편이다. 그러므로, SP를 사용할 경우는 인트라넷처럼 정해진 사용자의 수를 보장하는 서비스를 구현할 경우에 사용되는 경우가 많을 것이다.
- 서비스 확장을 위해 서버수를 늘릴경우 DB 수를 늘리는 것보다 WAS의 수를 늘리는 것이 더 효율적이기 때문에 대부분의 개발에서 DB에는 최소의 부담만 주고 대부분의 로직은 WAS에서 처리할 수 있게 한다.
4. 기타 부작용들이 존재한다. 비즈니스 로직적인 부분들이 DBMS 상으로 분리되었기 때문이다.
- 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 더 복잡해지고 유지보수 또한 어려워질 수 있다. 특히 개발, 스테이징, 운영으로 나누어 개발했을 경우, 운영 DB의 SP를 업데이트 하지 않았을 경우 배포가 정상적으로 동작하지 않는 것을 볼 수 있다.
- SP는 DBMS 의존적이므로, 추후 DB를 변경하기 힘들다.
Q. 언제 SP를 사용하는 것이 좋을까?
1. 테이블 관계가 복잡한 쿼리가 많을 경우에 유리하다.
- 한번 요청에, 여러개의 복합 Query문의 경우, 유용하다. 개별 테이블을 각각 불러와서, 웹서버에서 처리하는 것은 비효율적이다.
- 데이터가 많아지고, 테이블 관계가 복잡한 쿼리가 많을 경우, DB 안에서 한번에 처리하는 SP 쪽이 성능이 좋다.
- insert 쿼리문을 네트워크 통신을 통해 10번 날리는것과 insert 10번을 처리하는 SP를 네트워크 통신을 통해 1번 날리는것은 성능의 차이가 크다.
- 하나의 단일 작업을 위해서, 여러 테이블을 조인하는 경우, 여러 복잡한 연산을 수행하거나 쿼리를 연속적으로 실행할 때 유용하다.
- 반복해서 호출해야하는 경우에도 유용하다.
Q. SP 사용의 유형
1. 소스 내에 생SQL을 배제하고, 모든 쿼리는 SP로 처리하는 경우
- SP의 의존도가 높음.
2. 대부분 로직은 웹서버에서 돌리고 CRUD만 SP로 db에서 처리하는 경우
- SP의 의존도가 그나마 나음.
3. 상황에 따라, SP와 Web서버를 병행하는 경우
- 웹 쪽에서 할 때 계속 디비를 접근해야 하는 구조라면 그냥 SP로 작성하는 것이 좋을 듯 하고 처음에 데이터 가져오고 마지막에 결과만 넣어주면 된다 정도로 처리가 가능하다면 웹 쪽에서 처리하는 것도 괜찮다.
결론
ORM 썼더니 SP 도 안쓰고, 대부분의 쿼리도 직접 만들지 않아서 DB 검수 시 검수할 것도 매우 적고 심플해서 좋다.
ORM이 알아서, 중간 과정(쿼리 생성, 최적화)을 처리해주기 때문에, 추후에 DB도 손쉽게 변경 가능하고, 여러모로 좋다.
SP는 테이블들이 좀더 복잡해지는 단계의 프로젝트에 적합하다. DBA가 있어, 분업하여 개발할 경우 유익하다.
MSSQL 빠르게 정리하기
RDBMS 중 대표적인 서버인 MSSQL 서버에 대해서 공부해보자.
https://www.sqler.com/board_SQL2011/379541?page=4
개발자 커뮤니티 SQLER.com - [SQL2012강좌] 1. 코드명 Denali 설치 방법
이 내용은 2011년 6월 현재 SQL2012 (코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2012(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.아울러, 현재 Beta버전이지만 강좌를 따라해
www.sqler.com
설치
공식 홈페이지에서 다운로드 받을 수 있다. 설치 과정 중에 Instance 명을 지정할 수 있게 되는데, 이는 하나의 컴퓨터에서 여러개의 SQL서버를 돌릴 수 있고, 하나의 서버는 하나의 인스턴스명으로 구분되기 때문이다. 여러개의 SQL 서버를 구동할 것이 아니면, default instance로 지정하면 된다. 설치 공간과 용량을 지정하고, 윈도우 계정을 입력이 필요하다.
SQL Server는 Windows 서버에서 하나의 서비스 형태로 작동을 하게 되는데, 이때 이 서비스를 실행하기 위한 윈도우 계정이 필요하기 때문이다. SQL Server Agent 또한 SQL Server와는 다르게 또 다른 서비스의 형태로 작동하게 되는데, SQL Server Agent는 특정 시간에 원하는 구문을 처리한다거나, 백업 등을 담당하는 아주 중요한 서비스이다.
일반 적으로 SQL Server는 윈도우 계정 모드와 윈도우 계정 + SQL 계정 모드를 혼합할 수 있는 두가지 형태로 제공되고 있다.
윈도우 계정이라고 하는 것은 우리가 현재 설치를 하고 있는 윈도우 계정을 말하고, SQL 계정은 SQL Server 내에 계정을 생성해서 로그인을 할 수 있는 방법을 말한다.
여기서 2번째 혼합 모드로 선택을 하고, SQL Server의 기본 admin 계정인 sa의 비밀번호를 넣고,
현재 윈도우 계정을 Server admin으로 등록하기 위해 add Current User 를 클릭 후 Next를 누른다. 설치를 완료했을 경우, SQL Server가 동작할 수 있는 환경이 마련된다.
추후 SQL Server와 관련된 작업은 같이 설치된 SQL Server Management Studio(SSMS)라는 프로그램에서 주로 진행된다.
Server 접속
SQL 서버가 정상적으로 설치되었으면, SSMS라는 프로그램을 통해, 계정을 입력하고 SQL Server에 접속이 가능하다. 여기서 두가지 로그인 유형이 있는데, 설치시 윈도우 계정 + SQL 계정 모드를 혼합해서 설정하였을 경우, 두 유형으로 모두 접속이 가능하다. Ctrl + N으로 새로운 쿼리창을 생성해, 쿼리를 수행시켜볼 수 있다.
20210326
MSSQL DB 생성 방법, 프로시저 선언 및 사용법, GO 와 USE 문
DB 생성
좌측에 Database 디렉토리 항목에서 우클릭을 통해 New Database를 생성할 수 있다.
여기서 Database에 대한 간단하게 동작방식을 생각해보자. MSSQL 상에서 하나의 DB는 .MDF 파일과 .LDF 파일로 구성되어 있다. 데이터 트랜잭션의 무결성을 위해, 데이터가 변경되거나 수정되거나 삭제되었을때는 반드시 로그성 데이터를 기록하게 된다.
정전이나 기타 물리적인 이슈로 데이터 손실을 방지하기 위해 데이터베이스는 트랜잭션이라는 개념으로 데이터를 보존하게 된다. 데이터의 무결성을 보장하기 위해 트랜잭션은 ACID라는 속성을 가지고 있다.
SQL Server는 이런 데이터 손실을 방지하기 위해 LDF라는 확장자를 가진 곳에 데이터가 변경된 내역을 저장하게 된다.
따라서 SQL Server에서 데이터베이스를 생성하게 되면 기본적으로 MDF 와 LDF 하나씩은 만들어야 한다.
.MDF 라는 녀석은 파일 그룹에 속하게 되고, SQL Server 는 기본적으로 PRIMARY 라는 파일 그룹이 존재한다. 하나의 DB 안에 여러개의 파일그룹을 생성할 수 있으나, 파일그룹별 하나의 MDF 파일이 매칭되고, 파일 그룹당 하나의 .MDF 파일과 연관된 .LDF 파일이 생성된다.
물론 MDF가 한개 이상의 파일그룹을 가질 순 없고 무조건 1:1로만 가능하다. 기본적으로 DB는 하나의 파일 그룹 안에 .MDF라는 파일을 생성하여 저장되고, .MDF의 로그성 데이터인 .LDF 파일로 구성되어 있다. DB 안에 여러개의 파일그룹을 가질 수 있고, 파일 그룹별 .MDF 파일과 .LDF 파일이 추가된다. DB의 데이터들을 여러개의 파일에 나누어 저장할 수 있다는 의미이다.
아래는 DB를 생성하는데 필요한 기본 정보이다.
- Logical Name : DB의 논리적인 이름이다.
- File Name : DB의 실제 파일 이름이다.
- File Type : Rows Data(MDF 파일), Log(LDF 파일)
- Filegroup : 파일 그룹(기본값 PRIMARY)
- Initial Size (MB) : 기본적인 파일 사이즈(단위: MB)
- Autogrowth / Maxsize : 자동 증가 설정 및 최대 사이즈 설정 값
- Path : 실제 파일이 위치할 경로
기본적으로 DB를 생성하게 되면 기본적으로 최소 1개의 .MDF 파일과 최소 1개의 .LDF 파일이 생성된다. 각각의 파일은 논리적, 물리적 이름을 가지고 있고, 기본 파일 사이즈를 지정할 수 있다. 만약 데이터가 기본 사이즈보다 커질 경우, "자동증가설정"을 통해 N% 씩 용량이 증가 시키게 할 수도 있고, "최대사이즈"를 통해, 용량의 최대값을 지정할 수도 있다.
아래는 쿼리문을 통해, DB를 생성하는 방법이다.
CREATE DATABASE TestQuery ON
(
-- .MDF 파일 이름
NAME = N'TestQuery'
-- .MDF 파일 경로
, FILENAME = 'C:\TestQuery_DATA.MDF'
-- .MDF 파일 초기 기본 사이즈
, SIZE = 4MB
-- .MDF 파일 최대 용량 제한
, MAXSIZE = UNLIMITED
-- .MDF 파일 증가량(현재 용량보다 초과시, 1MB씩 증량)
, FILEGROWTH = 1MB
)
LOG ON
(
-- .LDF 파일 이름
NAME = N'TestQuery_log'
-- .LDF 파일 경로
, FILENAME = 'C:\TestQuery_LOG.LDF'
-- .LDF 파일 초기 기본 사이즈
, SIZE = 1MB
-- .LDF 파일 최대 용량 제한
, MAXSIZE = 1024GB
-- .LDF 파일 증가량(현재 용량보다 초과시, 10%씩 증량)
, FILEGROWTH = 10%
);
일일히 설정하는 것이 귀찮을 경우, system db 중에서 model db를 그대로 복제해서 만드는 방법도 있다.
CREATE DATABASE [데이터베이스명]
GO
저장 프로시저의 생성
"CREATE PROC <프로시저명> AS <sql문>" 라는 문법으로 아주 기본적인 프로시저를 생성할 수 있다.
CREATE PROC [ EDURE ] <dbo.procedure_name> [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS <SELECT ...> [ ...n ]
저장 프로시저의 수정
생성 SQL 문에서, "CREATE를 ALTER로 바꾸어 주면" 손쉽게 변경이 가능하다.
--프로시저 수정 구문
ALTER PROC <dbo.procedure_name>
AS
SELECT *
FROM dbo.test
GO
저장 프로시저의 삭제
생성 SQL 문에서, "CREATE를 DROP으로 바꾸어 주면" 손쉽게 삭제가 가능하다.
DROP PROC dbo.procedure_name
프로시저 생성구문 조회
아래의 명령어를 통해 프로시저의 생성 구문을 확인할 수 있다. 생성할 때 "WITH ENCRYPTION" 이라는 옵션을 주면, sp_helptext 명령어를 보내도 생성 구문을 암호화해서 보여주는 듯 한다.
SP_HELPTEXT <dbo.procedure_name>
저장 프로시저의 실행
아래의 명령으로 프로시저를 실행해볼 수 있다.
EXEC dbo.procedure_name
EXEC sp_tables @table_type = "'TABLE'";
-- @파라미터명 = '값' 으로 파라미터를 넘겨줄 수 있습니다.
EXEC sp_columns
@table_name = 'Department',
@table_owner = 'HumanReources';
저장 프로시져의 종류
저장 프로시저의 경우, 시스템 상에 이미 제공되는 것들또한 존재한다.
1. 사용자 정의 저장 프로시져
- 사용자가 생성한 프로시져를 의미한다.
2. 시스템 저장 프로시져
- sp_help, sp_helptext 와 같이 sp_로 시작하는 프로시져를 의미한다.
3. 확장 시스템 저장 프로시져
- xp_cmdshell 과 같이 xp_ 로 시작하는 프로시져를 의미 한다.
GO 유틸리티문
SQL Server 유틸리티 문 - GO - SQL Server
SQL Server 유틸리티 문 - GO
docs.microsoft.com
MSSQL에서 GO라는 문법이 사용되어 있는 것을 보았을 것이다. 왜 GO라고 지었는지, 무엇의 약자인지 잘 모르겠다. MS의 SQL Server는 JOB(작업) 배치라는 단위로 SQL을 실행하게 되는데, GO 명령어를 통해 하나의 작업 배치를 구분 지을 수 있다. GO 명령어는 명령어들의 END 위치에 오게 된다. 정리하면 GO는 (작업)배치단위를 구분 짓는 명령어로 사용된다. GO 명령어 뒤에 숫자를 사용하게 되면, GO 명령어 상위에 위치한 명령어 작업들을 여러번 반복하라는 의미이다.
GO 영역은 "GO와 GO 사이"를 의미한다. GO가 사용된 윗 부분의 일반 명령어들이 하나의 배치작업 단위가 되는 것이고, 하나의 배치로 해당 명령어들을 일괄처리하게 된다.
GO를 도대체 왜 쓰는거야?
매번 한줄 한줄 명령어를 실행할 때마다 DB에 접속해서 왔다갔다하면 처리시간도 많이 걸리고 효율적이지 못하다. 따라서 큰 덩어리 단위로, 묶음 단위로 처리할 것을 묶어서 보내주는 BATCH라는 개념이 나오게 된다. GO는 그 구분점이다. 한마디로, GO 이후에 명령어가 오면 다시한번 DB에 접속해서 처리한다는 의미일 것이다.
예를 들어, CREATE TABLE 문을 실행해서 테이블을 만들었다고 하자. 그런데 이 밑에 바로 INSERT문을 사용해서 해당 테이블에 데이터를 넣으면 에러가 나올 것이다. 일괄처리로 한번에 처리하려 하는데 테이블만드려하는데 옆에선 아직 생성중인 테이블에 바로 데이터 넣으려고할 수 있기 때문이다.
그러나, 소스 상에서, 프로시저, 뷰, 트리거 상에서 SQL과 함께 GO문을 사용할 수는 없다. 왜냐하면 정식 SQL 문이 아니고, SSMS 에서 스크립팅 작업을 할 때 편의상 쓰는 유틸리티문이기 때문이다.
소스상에서 sendCommand 두번 한 것이 배치가 두번 실행되었다는 것과 동일한 의미이고, 해당 작업을 SSMS 에서 테스트로 진행해보고 싶을 때, Statement 사이에 GO를 넣어서 돌려보는 것이다.
GO 의 분류
GO 문법의 경우, 독립적인 명령어로써 SQL문처럼 보이지만 사실 SQL이 아니다. 마이크로 소프트가 독자적으로 확장한 Transact-SQL도 아니다. 하기의 세가지 툴에서 쓰이는 tool용 명령어이다. (해당 도구에서만 사용할 수 있다는 의미겠지?)
GO 가 쓰이는 TOOL의 종류
1. SQL cmd 유틸리티
2. OSQL 유틸리티
3. SQL Server Management Studio 의 코드 편집기(SSMS)
즉, SSMS 편집기 상에서나 테스트할 때 쓰이는 문법이라는 의미이다.
SELECT ...
GO [count]
-- 에러 발생 :GO를 기준으로 다른 작업배치이므로, 변수를 공유할 수 없다.
DECLARE @num int
GO
SET @num = 10
-- 먼저 DB를 생성하는 배치를 수행하고, 해당 DB를 컨텍스트로 사용하도록 하는 배치를 수행
-- 아래의 경우, CREATE DATABASE 이후, GO로 한번 끊고 가야 정상 수행할 수 있다.
CREATE DATABASE [DB명]
GO
USE [DB명]
GO
-- 일괄 배치 안에, CREATE VIEW/TABLE/DATABASE 이후 해당 객체를 바로 사용 할수 없다.
CREATE TABLE [TABLE명]
GO
SELECT *
FROM [테이블명]
GO와 (;)의 차이
GO는 Batch 구분자이지만, (;)의 경우 Statement 구분자 이다.
Common Table Expression이란 무엇인가?(CTE)
https://www.essentialsql.com/introduction-common-table-expressions-ctes/
Common Table Expressions (Introduction to CTE's) - Essential SQL
Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries. Recursive CTE's can solve complex queries.
www.essentialsql.com
CTE란 SQL Server 상에서 사용되는 표현으로써, 복잡한 조인이나 서브쿼리를 단순화하여, 쿼리의 가독성을 높여주기 위해 사용되는 문법이다. 조직도와 같이 계층적 데이터를 쿼리하는데 효과적으로 사용될 수 있다.(recursive한 것에 강점이 있는 듯 하다.)
CTE는 항상 결과 Set을 반환한다. 사용의 주 목적은 쿼리를 단순화시키는 것이고, 메인 쿼리의 body에서 파생된 테이블을 제거시키는 것이다.
CTE는 View와 같이 동작하지만, DB 상의 VIEW와 다르게, 쿼리를 실행하는 동안에만 존재한다. 그래서, 해당 쿼리에만 국한된 내용일 때는 VIEW로 만들기보다, CTE로 만드는 것이 효과적이다.
CTE의 기본적인 사용법은 아래와 같다. 간단하게 "WITH [CTE테이블명] AS (SELECT ... )" 만 기억하자.
WITH expression_name_1 AS
(CTE query definition 1)
[, expression_name_X
AS (CTE query definition X) ,
etc ]
SELECT expression_A, expression_B, ...
FROM expression_name_1
구체적인 예시는 아래와 같다. ","를 추가하여 한번에 여러개의 CTE를 선언할 수도 있고, AS 문법을 쓸 필요 없이 바로 컬럼에 해당하는 이름을 지정할 수도 있다는 점을 알아두자. 먼저 생성된 SubQuery는 나중에 생성하는 SubQuery에서 사용할 수 있다는 것도 유의하자.
-- define CTE:
WITH Cost_by_Month
AS (SELECT campaign_id AS campaign, TO_CHAR(created_date, 'YYYY-MM') AS month, SUM(cost) AS monthly_cost
FROM marketing
WHERE created_date BETWEEN NOW() - INTERVAL '3 MONTH' AND NOW()
GROUP BY 1, 2
ORDER BY 1, 2)
-- 추가로 정의할 경우, WITH 없이 "," 후 추가
, Cost_by_Week AS (SELECT...)
-- AS 없이 바로 Column명을 지정할 경우, 자동으로 상응된다.
, Cost_by_Day(campaign, month) AS (SELECT campaign_id, TO_CHAR(created_date,'YYYY-MM-DD') FROM...)
-- use CTE in subsequent query:
SELECT campaign, avg(monthly_cost) as "Avg Monthly Cost"
FROM Cost_by_Month
GROUP BY campaign
ORDER BY campaign
Q. CTE는 또 왜 쓰는거야?
가독성 향상이 가장 큰 것 같다. 다만 동일한 파생 테이블이 다른 쿼리에서도 자주 쓰인다면, CTE로 매번 선언할 것이 아니라, VIEW를 정의하는 것이 낫다는 것만 유의하자. SQL Server와 Oracle에서 지원하는 문법인 듯하다.
1. 가독성 향상이다.
- 하나의 복잡한 쿼리를 여러개의 CTE로 선언 후, CTE들을 결합하여, 쿼리를 수행해냄으로써 쿼리 이해를 높여준다.
2. 뷰에 대한 대체제이다.
- 뷰를 생성하기 위한 권한이 필요없고, CTE의 경우 저장되지 않고, 하나의 쿼리 안에서만 사용되고 사라진다. 필요하면 재사용되기도 한다. 하나의 싱글 쿼리에서, 여러번 참조될 때 Subquery로 Main 쿼리에 Body에 매번 넣는 것보다 효과가 좋다.
3. 재귀 작업에 유용하다.
- 자기 자신을 호출하는 재귀적인 쿼리를 수행하는데, 유용하다. 특히 조직도의 데이터 쿼리에 유용하다.
Q. CTE에서 WITH 문 앞에 (;)을 자주 붙이는 이유는 무엇일까?
WITH라는 예약어가 다른 SQL 문법과도 같이 쓰이는 경우가 많으므로, WITH문으로 시작하는 SQL문이면 상관 없지만, WITH 위에 다른 SQL문이 있을 경우, 상위 SQL문과 혼합되지 않도록 (;)을 추가하는 것을 잊으면 안된다. 그래서, 코드 중에 ;WITH로 시작하는 것들을 많이 볼 수 있다.
Q. USE문을 사용하는 이유는 무엇일까?
기본적으로 SQL Server를 설치할 때, 설정했던 Instance라는 개념이 있다. Instance당 하나의 서버를 의미하는 듯 하다.
하나의 Instance는 여러개의 database를 생성할 수 있다. 하나의 database는 여러개의 schema를 지정할 수 있다. 기본적으로 SQL Server 상에서는, DataBase Owner(DB 소유자)를 의미하는 dbo가 기본 스키마로 지정되어 있다. 우리가 기본적으로 "SELECT * FROM [테이블명]"을 호출할 때, 내부적으로는 [테이블명]이 [인스턴스명].[데이터베이스명].[스키마명].[테이블명]을 의미한다.
SSMS 상에서 "USE [데이터베이스명]" 명령어를 DBMS에 보내면 SSMS 상에서 또 다른 USE를 사용하지 않는 한, 추후 SSMS 상에서 수행되는 모든 쿼리문을 해당 DB를 대상으로 수행된다. 인스턴스 내부에 Database가 하나라면 상관없겠지만, 여러개일 경우 USE 명령어를 종종 사용하게 된다. SQL Server 상에서, 스키마 이름을 생략하게 되면 아래와 같이 디폴트 스키마인 'dbo'를 자동으로 붙이게 된다. 만약 default 스키마가 아닐 경우, 에러가 발생할 것이다.
Q. SQL문의 Alias(AS)의 여러가지 문법에는 무엇이 있는가?
표준은 해당 컬럼, 테이블명 뒤에 AS '별칭' 을 붙이는 것이다. 그러나, 늘 편한 문법이 존재한다. AS 없이도 띄어쓰기를 하고 텍스트를 쓰면 해당 별칭이 적용된다. 그러나, 별칭에 빈칸이 존재한다면? 무조건 ''(작은 따옴표)로 묶어주거나, [](대괄호)로 묶어주어야 한다. SQL Server는 가독성 때문에 []를 선호하는 듯하다. 반면에 별칭에 빈칸이 존재하지 않는다면, 작음 따옴표나 대괄호로 묶어줄 필요가 없다는 의미이기도 하다.
-- 표준
SELECT DepartmentID AS 부서번호...
-- 편의1
SELECT Name 부서이름
SELECT Name '부서이름'
SELECT Name [부서이름]
-- 띄어쓰기가 있는 경우
SELECT Name '부서 이름'
SELECT Name [부서 이름]
-- 이런 방식도 있네
SELECT [부서이름]=GroupName, Name, Age ...
SQL문의 주석(Comment) 처리는 어떻게 하는가?
단일행 주석은 (--)을 사용하고, 멀티행 주석은(/**/)을 사용한다고 한다.
20210329
Q. SQL과 T-SQL의 차이는 무엇일까?
SQL을 배우기 시작하는데 앞서 기존의 해왔던 순차적으로 데이터를 하나씩 처리하는 방법을 깨뜨리고 데이터 집합 별로 한 번에 처리 할 수 있는 시각을 가져야만 한다. 제품별로 특징을 가진 SQL 문을 사용한다. 이것은 데이터 조작을 더욱 더 정교하고 다양하게 하기 위함이다. 대부분의 제품별 SQL 문은 서로 기본적인 개념은 모두 동일하다.
SQL-Server에서 사용되는 SQL 문은 Transact-SQL이다. 줄여서 T-SQL이라고 불린다. SQL 문을 만들 때마다. 더 좋은 방법은 없을까라고 생각하는 노력만이 SQL 문을 자유자재로 사용할 수 있게 해줄 것이다.
-- SQL이 집합처리 된다는 사실을 알지 못하는 경우
UPDATE test_table
SET test_attr = '2'
WHERE test_attr = '1';
UPDATE test_table
SET test_attr = '3'
WHERE test_attr = '2';
UPDATE test_table
SET test_attr = '4'
WHERE test_attr = '3';
-- SQL이 집합처리 된다는 사실을 아는 경우(더 빠르다)
UPDATE test_table
SET test_attr = test_attr + 1
WHERE test_attr <= 3 AND test_attr >= 1 개발자들이 일반적으로 실수하는 것 중에 하나는, 프로그래밍 로직을 관계형 데이터베이스 SQL에 접목하려고 한다는 점이다. 그러나, SQL이 순차적 처리가 아닌 집합별로 한번에 처리하는데 용이하게 설계되었음을 깨닫게 된다면, 프로그래밍 로직으로 구현된 것을 SQL 형태로 바꾸기 위해 노력을 할 것이다. 그리고 그것이 더 효율적이고 정확하다는 것을 알 게 될 것이다.
20210330
JOIN에 대하여 알아보자.
데이터의 무 결성을 위해서는 관계를 맺는 컬럼들을 제외하고는 데이터의 중복을 최소화해야 한다. 불필요한 데이터의 중복은 DBMS와 개발자들을 고생시키고, 차후에는 데이터의 정합성이 떨어지게 되며 결국에는 데이터베이스를 믿을 수 없게 만들 것이다. 데이터의 중복을 피하기 위해서는 각각의 테이블들이 하나의 주제만 담을 수 있도록 테이블들을 나누어야 한다. 그리고 다시 테이블들을 연결하기 위해서는 조인이란 연산을 사용 할 수 있을 것이다. 데이터 중복이 많은 시스템에서는 상대적으로 조인의 횟수가 줄어 들 수 있겠지만, 어떤 시스템이든 조인의 필요성은 있는 것이다.
JOIN의 기본적인 개념은 조건을 만족하는 데이터끼리 데이터를 연결하는 연산이다. 아래의 예시를 보자.
1. Orders 라는 OrdersId를 PK로 하는 Master 테이블이 있고, OrdersDetail이라는 DetailId를 PK로 하고, OrdersId를 FK로 가지고 있는, Detail 테이블이 있다.
2. 각 Orders에 대한 Detail 정보까지 알기 위해서, JOIN이 필요하다.
3. JOIN 조건은 Orders.OrderID = [OrderDetails].OrderID일 것이다. 데이터 결합이 일어 난다.
4. JOIN이 어떻게 이루어질지 "생각"해보자. Details 테이블에는 동일한 OrderID를 가질 수 있을 것이다.
5. 데이터 건수가 작은 쪽의 데이터는 데이터 건수가 많은 쪽의 데이터와 동일한 건수로 만들어 지게 된다.
즉 결과는, Orders 쪽의 데이터가 Order Datails 쪽의 데이터만큼 늘어난 것처럼 보이고, 동일한 OrderID를 가진 Master 쪽 나머지 데이터들은 다 동일한 값을 가지고 있을 것이므로, 중복이 많이 발생한 것처럼 보일 수 있다.
JOIN의 기본적인 문법은 무엇일까?단순하게, FROM [A테이블명] JOIN [B테이블명] ON A.ID = B.A_ID 을 기억하자.
SELECT A.OrderID, A.CustomerID, B.OrderID, B.ProductID
FROM Orders A JOIN [Orders Details] B
ON A.OrderID = B.OrderID
WHERE A.OrderID IN (10000,12000)
--
FROM [A테이블명] JOIN [B테이블명] ON A.ID = B.A_ID
표준문법인 ANSI 구문에 익숙해지는 것이 낫다. JOIN 조건을 WHERE문에 넣는 문법은 T-SQL 방식이다.
JOIN에는 다양한 유형이 있다.
1. <A> [INNER] JOIN <B> ON a.id = b.a_id -> A 교집합 B
2. <A> LEFT, RIGHT, FULL [OUTER] JOIN <B> ON a.id = b.a_id -> A, B, A 합집합 B
3. <A> CROSS JOIN <B> -> MxN, 따로 조인컬럼이 필요 없음
4. 차집합은 따로 OUTER JOIN 이후, WHERE로 a.id IS NULL 과 같은 조건을 줌으로써 가능
우리에게 가장 익숙한 JOIN은 INNER JOIN(내부 조인)이다. 현업에서 JOIN한다고 하면 그냥 70%는 내부 조인이구나라고 생각하자.
두 집합(Table) 사이의 교집합인 것이다. 양쪽 Table Row들 중에서 조인 조건에 반드시 매칭이 되는 Row만 살아남는다. A테이블의 각 row들이 B 테이블의 row와 무조건 1건 이상은 매칭이 되어있다는 보증이 없다면, 단순히 JOIN만 하면, A 테이블의 row가 유실될 수 있음에 유의해야 한다.
내부 조인의 경우, A 또는 B 테이블 Row에 손실이 발생할 수 있다. A 테이블의 조인 컬럼값이 B 테이블 내에 하나도 존재하지 않더라도, (B 테이블에 해당하는 값은 NULL로 표시하더라도) A 테이블의 값은 모두 표시되도록 할 수는 없을까? 그러한 성격의 JOIN을 위해서 OUTER JOIN(외부 조인)이 존재한다. LEFT OUTER JOIN 일 경우, JOIN절을 기준으로 왼쪽 테이블의 데이터는 오른쪽 테이블과 매치된 건이 없더라도 무조건 살리는 방향으로 조인을 수행한다. RIGHT OUTER JOIN 일 경우, 반대로 오른쪽 데이블의 데이터를 무조건 살린다.(누락이 없도록 한다.) 외부 조인은 주체가 되는 모든 결과를 내보내 주며 조인 결과에 만족치 않는 상대 테이블의 데이터에는 NULL 값을 돌려주게 된다. 어느 한쪽에 있는 데이터가 모두 나와야 할 때는 외부 조인을 사용해야함을 잊지 말자.
LEFT OUTER JOIN
-- A 테이블의 데이터는 모두 나오고, B 테이블과 매치되는 건은 표시되고, 아닌 Row는 NULL로 표시됩니다.
RIGHT OUTER JOIN
-- B 테이블의 데이터는 모두 나오고, A 테이블과 매치되는 건은 표시되고, 아닌 Row는 NULL로 표시됩니다.
FULL OUTER JOIN
-- A 테이블과 B 테이블의 데이터가 매치여부에 상관없이 모두 나오고, 매치되는 건 이외의 Row들 중 없는 값은 NULL 로 표시됩니다.
-- ANSI 구문
SELECT A.CustomerID, B.OrderID
FROM Customers A LEFT OUTER JOIN Orders B
ON A.CustomerID = B.CustomerID
-- MSSQL에서 가능(Transact-SQL)
SELECT A.CustomerID, B.OrderID
FROM Customers A, Orders B
WHERE A.CustomerID *= B.CustomerID
-- T-SQL 상에서는, FROM 절에 ',' 기호로 여러개의 테이블을 참조할 수 있습니다.
-- T-SQL 상에서는, WHERE 절에 JOIN 조건을 넣을 수 있습니다.
-- T-SQL 상에서는 '*=' 기호로 LEFT OUTER JOIN을 구현할 수 있습니다.
마지막은 CROSS JOIN으로 M*N개의 Row를 반환하는 조인컬럼이 따로 필요없는 경우이다. 성능이 느리기도 하고 현업에서는 거의 볼 수 없다. 셀프 조인을 위해 CROSS JOIN을 사용하기도 한다.
SELECT A.ID, A.NAME, B.ID, B.NAME
FROM A CROSS JOIN B
-- M x N 개의 row를 반환합니다.선뜻 헷갈릴 수도 있는 구문을 정리해보자. LEFT INNER JOIN이란 존재하지 않는다. T-SQL 상에서는, OUTER와 INNER를 생략해도 무방하기 때문에, LEFT JOIN과 LEFT OUTER JOIN이 다른 역할을 한다고 착각할 수 있다. 그리고, INNER JOIN과 JOIN이 다르다고 착각할 수도 있다.
A LEFT JOIN B == A LEFT OUTER JOIN B
A RIGHT JOIN B == A RIGHT OUTER JOIN B
A FULL JOIN B == A FULL OUTER JOIN B
A INNER JOIN B == A JOIN BMSSQL T-SQL과 SQL 표준 구문의 차이점을 알아보자.
-- ANSI 구문
SELECT A.CustomerID, B.OrderID
FROM Customers A LEFT OUTER JOIN Orders B
ON A.CustomerID = B.CustomerID
WHERE B.OrderID IS NULL
-- 표준 SQL 문에서는 JOIN 이후에, WHERE절이 수행됩니다.
-- MSSQL에서 가능(Transact-SQL)
SELECT A.CustomerID, B.OrderID
FROM Customers A, Orders B
WHERE A.CustomerID *= B.CustomerID AND B.OrderID IS NULL
-- T-SQL 상에서는 JOIN 이전에, JOIN할 테이블을 대상으로 먼저 WHERE절이 수행됩니다.
-- T-SQL 상에서는 세개 이상의 테이블을 외부 조인으로 결합시, 동작하지 않습니다.
한 눈으로 보는 JOIN 정리
Q. 3개 이상의 테이블은 어떻게 JOIN할 수 있을까?
아래의 경우는 4개의 테이블의 JOIN 예시이다.
SELECT A.A_COL, B.B_COL, C.C_COL, D.D_COL
FROM A JOIN B ON A.ID = B.A_ID
JOIN C ON B.ID = C.B_ID
JOIN D ON C.ID = D.C_ID
WHERE A.A_COL = "12345";
-- B 테이블이 A_ID를 FK로 가지고 있고,
-- C 테이블이 B_ID를 FK로 가지고 있고,
-- D 테이블이 C_ID를 FK로 가지고 있다고 가정할 때 Q. 다중 JOIN시 주의해야할 점은 무엇일까?
1. OUTER JOIN 쿼리는 무조건 뒤로 뺀다. INNER JOIN 을 모두 마치고 OUTER JOIN 들이 후속으로 나와야 쿼리 속도가 개선된다. 아직 완전히 이유는 모르겠지만, INNER JOIN으로 최대한 Row 수를 줄인 상태에서, OUTER JOIN을 수행하는 것이 속도에 유리한 듯 하다.
INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로(교집합이니까), MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 하지만 OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 선택할 수 없다. 그래서, 성능 상의 이슈가 있을 수 있다. OUTER JOIN은 로직상, 외부 for문이 OUTER가 되는 테이블이 되어야 하기 때문이다.
2. 무조건 메인테이블이 먼저 나오고, 후속 테이블이 뒤에 나와야 한다. 어느 쿼리든 특정 Master 테이블을 기본으로, 부가적인 정보를 위해 후속 테이블을 조인해서 조회해온다. Master에 해당하는 테이블을 먼저 잘 식별하고, JOIN 문에서 선행시켜야 잘못 구현하는 일이 없을 것이다.
JOIN의 순서에 대해 생각해보자.
JOIN 쿼리는 기본적으로 Nested Loop 방식으로 작동한다는 배경 지식이 필요하다. Nested Loop 는 간단히 말해서 이중 for 문이다.
FOR ( record1 IN TABLE1 ) { // 외부 루프 (OUTER)
FOR ( record2 IN TABLE2 ) { // 내부 루프 (INNER)
IF ( record1.join_column == record2.join_column ) {
join_record_found(record1.*, record2.*);
} ELSE {
join_record_found(record1.*, NULL);
}
}
}
출처: https://12bme.tistory.com/165 [길은 가면, 뒤에 있다.]
// Table A의 Row개수 적을수록 유리하다.
// Table A를 빨리탐색할수록 유리하다.
// 반복문의 기점이 되는 Table A를 "드라이빙 테이블"이라고 호칭한다.
// 그 이후에 오는 모든 Table을 "드리븐 테이블"이라고 호칭한다.
// Driving Table을 잘 지정하는 것이 중요하다.(SQL Optimizer가 왠만하면 잘 조정해주지만)
TABLE_A 와 TABLE_B 가 JOIN 을 하게 된다. 위 for문을 기준으로 설명해보겠다. 처음으로 가지고 오는 테이블 인 TABLE_A가 반복문의 기준점이 되는데 이러한 테이블을 드라이빙 테이블이라고 한다. TABLE_B 는 드리븐 테이블이라고 한다. 정리하면 옵티마이저가 먼저 가져오기로 선택한 테이블이 드라이빙이며, 그 후 가져오는 모든 테이블은 드리븐이다.
JOIN의 연결 조건에는 = 이외에도 여러 다른 조건(AND, >=) 등이 가능하다는 점을 잊지 말자.
= 조건만 사용하다 보면 조인 조건에 다른 조건이 들어갈 수 있다는 것을 잊게 되기 때문이다. 그러나 다른 조건은 심각한 OVERHEAD를 가져올 수 있으니, 주의해서 사용해야 한다.
JOIN 연산은 테이블을 연결한다는 것에 초점을 맞추자.
JOIN 연산은 두개의 테이블을 연결하게 되고, 해당 테이블의 연결 조건은 ON 절 다음에 명시해주게 된다. 대부분이 조인이 되어지는 테이블들은 서로 관계가 맺어져 있는 테이블들이고. 그리고 조인되는 조건으로 사용되는 컬럼 또한 서로 FK(외래키)/PK(주키) 형태로 서로 관계가 맺어져 있는 컬럼들이다.
물론 테이블 생성시, FK 조건을 걸지 않고도, 타 테이블의 PK를 하나의 컬럼으로 저장할 수 있겠지만, FK 조건을 걸어줌으로써, Table 사이의 관계를 정의해주어야 추후 참조하는 테이블의 데이터를 삭제할 경우에 CASCADE SET NULL 등의 데이터 무결성을 위한 조건들을 줄 수 있다. 관계가 맺어져야 조인이 가능한 것은 아니지만, 관계가 맺어져 있는 컬럼들 간에 조인이 빈번히 일어나는 것이다.
Tip. JOIN 방식 참 다양하구먼.
-- QUERY1. INNER JOIN (T-SQL 방식)
SELECT d.deptno ,d.dname ,e.ename ,e.sal
FROM dept d, emp e
WHERE d.deptno = e.deptno AND e.sal > 2000;
-- Cartesian Product(카테이시안 곱)에 의한 조인에서 WHERE절에서 조건을 비교함
-- QUERY2. INNER JOIN (ON절에 JOIN 조건으로 ID 매칭만, 나머지 조건은 WHERE절에서 수행)
SELECT d.deptno ,d.dname ,e.ename ,e.sal
FROM dept d [INNER] JOIN emp e ON d.deptno = e.deptno
WHERE e.sal > 2000;
-- deptno에 대한 조인 작업을 한 후 WHERE 절에서 sal에 대한 비교함
-- QUERY3. INNER JOIN (ON절에 JOIN 조건으로 ID 매칭 이외에, 기타 조건 수행)
SELECT d.deptno, d.dname ,e.ename ,e.sal
FROM dept d [INNER] JOIN emp e ON d.deptno = e.deptno AND e.sal > 2000;
-- ON절에서 조인할때 모든 조건을 비교함
-> 결론 INNER JOIN의 경우, 어차피 교집합(모든 조건이 일치해야함)이므로 결과나 성능은 똑같다!
-> 그러나, OUTER JOIN의 경우, 조건의 위치가 성능이나 올바른 결과면에서 중요하다.
SELECT d.deptno, sum(e.sal)
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno
WHERE e.sal > 2000
GROUP BY d.deptno
ORDER BY d.deptno;
-- WHERE 조건 중 "e.sal > 2000"이 e.sal 값이 Null일 경우, 결과에서 제거시켜버리기 때문에,
-- OUTER JOIN임에도 불구하고, emp와 매치되지 않은 dept row를 받아볼 수 없다.
SELECT d.deptno, sum(e.sal)
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno AND e.sal > 2000
GROUP BY d.deptno
ORDER BY d.deptno;
-- OUTER JOIN의 조인 조건으로 e.sal > 2000이 포함되기 때문에, 매치되지 않는 dept 테이블의 값은
emp 데이터를 null인 상태로 표시된 상태로 조회된다.
-- OUTER JOIN의 의도가 dept 테이블을 모두 보여주는게 목표이므로, 아래와 같은 방식으로 수행하는 것이 중요하다.SQL문의 쿼리 처리 순서는 무엇일까?
1. SELECT
2. FROM -> ON -> JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
SQL의 성능에 대해서 알아보자.
데이터베이스 엔진 자체에서 데이터 어떻게 찾아오는게 좋을지 판단하는 커스터마이저가 있다. DBMS는 내부적으로 테이블별 데이터 분포도와 같은 데이터 통계정보를 관리하는 듯하다. 그리고, 사용자가 JOIN과 같이 코스트가 많이 드는 쿼리를 요청했을 경우, 옵티마이저가 어느 테이블을 먼저 조회해야하는지를 내부적으로 선택해서 빠른 것으로 처리하게 된다.
- 오라클 등 커스터마이저가 있는 DB => 상관 없음. (예로 오라클, POSTGRES)
- 심플하고 단순무식한 DB => 상관있을수 있음.(MYSQL, SQLITE)
조인 시에 성능을 위해서는 로우의 개수가 적은 테이블부터 읽는게 일반적으로 성능이 빠르다. 그리고 먼저 읽는 테이블을 '드라이빙 테이블' 이라고 부른다.
'[CS] IT 용어 모음' 카테고리의 다른 글
| SSL 인증서란? (0) | 2021.05.28 |
|---|---|
| 지식관리_ASP.NET_웹폼 (0) | 2021.04.07 |
| 지식관리_방법론 (0) | 2021.03.26 |
| [IT용어] CQRS[Command and Query Responsibility Segregation]과 이벤트 소싱[Event Sourcing]이란? (0) | 2020.11.04 |
| 콜백[Callback] 함수란? (0) | 2020.07.22 |
최근댓글