INNER JOIN

1
2
3
4
5
6
7
8
9

SELECT 
    l.Title,
    r.Name
FROM 
    albums AS l
INNER JOIN
    artists AS r
ON 
    r.ArtistId = l.ArtistId;

1
2
3
4
5
6

SELECT
   Title, 
   Name
FROM
   albums
INNER JOIN artists USING(ArtistId);

LEFT JOIN

1
2
3
4
5
6
7
8

SELECT
    Name, 
    Title
FROM
    artists
LEFT JOIN albums ON artists.ArtistId = albums.ArtistId
ORDER BY 
    Name;

SELF JOIN

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Receives reports from'
FROM
    employees e
INNER JOIN 
    employees m 
ON 
    m.employeeid = e.reportsto
ORDER BY 
    manager;
  • ‘A 테이블’과 A 테이블의 복사본인 ‘B 테이블’을 합치기

Grouping Data

agg-func

1
2
3
4
5
6
7
8
9

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
GROUP BY
    albumid
ORDER BY 
    COUNT(trackid) DESC;

1
2
3
4
5
6
7
8
9

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
GROUP BY
    albumid
HAVING
    albumid = 1;

1
2
3
4
5
6
7
8
9

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
WHERE
    COUNT(trackid) BETWEEN 18 AND 20
GROUP BY
    albumid;
  • 에러발생: WHERE문에는 집계함수 사용 불가
  • WHERE가 집계함수보다 우선적으로 실행되기 때문
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    tracks.albumid,
    title,
    MIN(tracks.milliseconds),
    MAX(tracks.milliseconds),
    ROUND(AVG(tracks.milliseconds), 2)
FROM
    tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
    tracks.albumid;
  • ROUND는 n번째 자리까지 나타나도록 반올림

Subquery

1
2
3

SELECT AVG(SUM(bytes))
FROM tracks
GROUP BY albumid;
  • SELECT 문에서 집계함수의 결과 값에 바로 중첩하여 집계함수 적용 불가
1
2
3
4
5
6
7
8
9

SELECT
    AVG(SIZE)
FROM
    (SELECT
         SUM(bytes) AS SIZE
     FROM
         tracks
     GROUP BY
         albumid);