[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 1장. SQL 기본 - 9. 조인(JOIN)
=
연산자를 사용해서 표현SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;
SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1 INNER JOIN 테이블2
ON 테이블1.칼럼명1 = 테이블2.칼럼명2;
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER, TEAM WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER INNER JOIN TEAM
ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, P.TEAM_ID 팀코드,
T.TEAM_NAME 팀명, T.REGION_NAME 연고지
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID;
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, P.TEAM_ID 팀코드,
T.TEAM_NAME 팀명, T.REGION_NAME 연고지
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID;
SELECT P.PLAYER_NAME 선수명, T.REGION_NAME 연고지, P.BACK_NO 백넘버, T.TEAM_NAME 팀명
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID AND P.POSITION = 'GK'
ORDER BY P.BACK_NO;
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, T.REGION_NAME 연고지, T.TEAM_NAME 팀명
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID
WHERE P.POSITION = 'GK'
ORDER BY P.BACK_NO;
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID,
STADIUM.STADIUM_NAME, STADIUM.SEAT_COUNT
FROM TEAM, STADIUM
WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID;
// INNER JOIN을 명시하여 사용할 수도 있다.
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID,
STADIUM.STADIUM_NAME, STADIUM.SEAT_COUNT
FROM TEAM INNER JOIN STADIUM
ON TEAM.STADIUM_ID = STADIUM.STADIUM_ID;
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID,
S.STADIUM_NAME, S.SEAT_COUNT
FROM TEAM T, STADIUM S
WHERE T.STADIUM_ID = S.STADIUM_ID;
// INNER JOIN을 명시하여 사용할 수도 있다.
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID,
S.STADIUM_NAME, S.SEAT_COUNT
FROM TEAM T INNER JOIN STADIUM S
ON T.STADIUM_ID = S.STADIUM_ID;
=
연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행한다.SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;
// INNER JOIN을 명시하여 사용할 수도 있다.
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID
INNER JOIN STADIUM S
ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;