SQL 정리 (JOIN, 연산자)

 

//====

테스트 데이터베이스

- 예제 테이블

 

* users 테이블

mysql> SELECT * FROM users;

+----+-------+

| id | name  |

+----+-------+

|  1 | user1 |

|  2 | user2 |

|  3 | user3 |

+----+-------+

 

* tasks 테이블

mysql> SELECT * FROM tasks;

+----+-------+---------+

| no | task  | user_id |

+----+-------+---------+

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 13 | task4 |       4 |

| 14 | task5 |       1 |

+----+-------+---------+

 

 

//==========

//JOIN(조인,집합) 개념 다이아그램

https://www.w3schools.com/sql/sql_join.asp

 

 

//===========

* CROSS JOIN (교차) - 곱집합

    -  ON 조건없는 JOIN, 풀스캔 주의

 

mysql> SELECT * FROM tasks CROSS JOIN users;

 

- 동일: SELECT * FROM tasks , users;

 

- 4 x 3 = 12개 결과

+----+-------+---------+----+-------+

| no | task  | user_id | id | name  |

+----+-------+---------+----+-------+

| 11 | task1 |       1 |  1 | user1 |

| 11 | task1 |       1 |  2 | user2 |

| 11 | task1 |       1 |  3 | user3 |

| 12 | task2 |       2 |  1 | user1 |

| 12 | task2 |       2 |  2 | user2 |

| 12 | task2 |       2 |  3 | user3 |

| 13 | task4 |       4 |  1 | user1 |

| 13 | task4 |       4 |  2 | user2 |

| 13 | task4 |       4 |  3 | user3 |

| 14 | task5 |       1 |  1 | user1 |

| 14 | task5 |       1 |  2 | user2 |

| 14 | task5 |       1 |  3 | user3 |

+----+-------+---------+----+-------+

 

 

//=====

- 다른 테이블에 같은 이름의 칼럼이 있는 경우

-> AS (Alias)로 이름 지정

 

예) SELECT t.id AS tid, t.task, u.id AS uid, u.name FROM tasks AS t , users AS u

+-----+-------+-----+-------+

| tid | task  | uid | name  |

+-----+-------+-----+-------+

|  11 | task1 |   1 | user1 |

...

 

 

 

//============

* INNER JOIN (내부) - 교집합

    - ON 조건 있는 조인

 

mysql> SELECT * FROM tasks INNER JOIN users ON (users.id = tasks.user_id);

- 동일) SELECT * FROM tasks , users WHERE (users.id = tasks.user_id);

+----+-------+---------+----+-------+

| no | task  | user_id | id | name  |

+----+-------+---------+----+-------+

| 11 | task1 |       1 |  1 | user1 |

| 12 | task2 |       2 |  2 | user2 |

| 14 | task5 |       1 |  1 | user1 |

+----+-------+---------+----+-------+

 

 

//========================

* LEFT JOIN  (외부1)

- 일치하는 값이 없어도 왼쪽 테이블은 모두 가져옴

- ON 의 조건에 맞지 않은 오른쪽 테이블의 레코드는 NULL 로 설정됨

 

mysql> SELECT * FROM tasks LEFT JOIN users ON (users.id = tasks.user_id);

+----+-------+---------+------+-------+

| no | task  | user_id | id   | name  |

+----+-------+---------+------+-------+

| 11 | task1 |       1 |    1 | user1 |

| 14 | task5 |       1 |    1 | user1 |

| 12 | task2 |       2 |    2 | user2 |

| 13 | task4 |       4 | NULL | NULL  |

+----+-------+---------+------+-------+

 

- WHERE 사용 해서 필터

mysql> SELECT * FROM tasks LEFT JOIN users ON (users.id = tasks.user_id)

    -> WHERE  (users.id = tasks.user_id);

 

 

//======

* RIGHT JOIN (외부2)

 

mysql> SELECT * FROM tasks RIGHT JOIN users ON (users.id = tasks.no);

+------+------+---------+----+-------+

| no   | task | user_id | id | name  |

+------+------+---------+----+-------+

| NULL | NULL | NULL    |  1 | user1 |

| NULL | NULL | NULL    |  2 | user2 |

| NULL | NULL | NULL    |  3 | user3 |

+------+------+---------+----+-------+

 

 

//============

* FULL OUTER JOIN - 합집합

- MySQL에서 지원하지 않음

- UNION 연산자로 해결

 

 

//================================

* UNION (집합 연산자) 

- 두 테이블을 합침, 

- 칼럼 갯수가 동일해야 함, 같은 종류의 칼럼을 사용해야함 

- UNION : 중복 제거, 

- UNION ALL : 중복 제거 안함

 

mysql> SELECT no, task FROM tasks  UNION (SELECT id, name FROM users);

+----+-------+

| no | task  |

+----+-------+

| 11 | task1 |

| 12 | task2 |

| 13 | task4 |

| 14 | task5 |

|  1 | user1 |

|  2 | user2 |

|  3 | user3 |

+----+-------+

- 주의! : no와 id 가 겹쳐짐, 

같은 종류의 데이터를 지정 해야 함

 

 

//==============================================

* GROUP BY

- 중복자료 제거, 정렬 작업

- SELECT 절에 함수(Aggregation, 집합연산함수) 사용가능

 

SELECT user_id FROM tasks GROUP BY user_id;

+---------+

| user_id |

+---------+

|       1 |

|       2 |

|       4 |

+---------+

 

 

* DISTINCT

- 중복자료 제거, 정렬 안함

SELECT DISTINCT user_id FROM tasks;

+---------+

| user_id |

+---------+

|       1 |

|       2 |

|       4 |

+---------+

 

//==============

* DISTINCT 와 GROUP BY의 차이

https://intomysql.blogspot.com/2011/01/distinct-group-by.html

- DISTINCT로만 가능한 기능

SELECT COUNT(DISTINCT fd1) FROM tab;

      -- 이런 형태의 쿼리는 서브 쿼리를 사용하지 않으면 GROUP BY로는 작성하기 어렵다.

 

 

- GROUP BY로만 가능한 기능

SELECT fd1, MIN(fd2), MAX(fd2) FROM tab GROUP BY fd1;

      -- 이렇게 집합함수(Aggregation)가 필요한 경우에는 GROUP BY를 사용해야 한다.

 

 

//=============================

//결과값 참고

mysql> SELECT user_id , COUNT(user_id) FROM tasks GROUP BY user_id;

+---------+----------------+

| user_id | COUNT(user_id) |

+---------+----------------+

|       1 |              2 |

|       2 |              1 |

|       4 |              1 |

+---------+----------------+

 

mysql> SELECT DISTINCT user_id , COUNT(user_id) FROM tasks;

+---------+----------------+

| user_id | COUNT(user_id) |

+---------+----------------+

|       1 |              4 |

+---------+----------------+

 

mysql> SELECT user_id , COUNT(DISTINCT user_id) FROM tasks;

+---------+-------------------------+

| user_id | COUNT(DISTINCT user_id) |

+---------+-------------------------+

|       1 |                       3 |

+---------+-------------------------+

 

 

//===================

* HAVING

- WHERE 과 같지만 집합함수를 사용 가능

- WEHRE은 함수를 사용 불가

- GROUP BY 와 집합함수에 영향을 미침

 

 

mysql> SELECT user_id FROM tasks WHERE  MIN(user_id);  <== 에러

1111 - Invalid use of group function

 

mysql> SELECT user_id FROM tasks HAVING  MIN(user_id);

+---------+

| user_id |

+---------+

|       1 |

+---------+

 

 

 

//===============

- 기타 연산자

 

* IN 

- 하나라도 만족하면 true

SELECT * FROM tasks WHERE user_id IN( 1, 2 );

+----+-------+---------+

| no | task  | user_id |

+----+-------+---------+

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

+----+-------+---------+

 

 

* ANY (=SOME)  (or 연산)

- 하나라도 만족하면 true

- IN  과 다른점 : 비교 연산자를 사용 가능

mysql> SELECT * FROM tasks WHERE user_id <= ANY ( SELECT id FROM users WHERE id <= 2);

+----+-------+---------+

| no | task  | user_id |

+----+-------+---------+

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

+----+-------+---------+

 

 

* ALL   (and 연산)

- 모두 만족해야만 true

SELECT * FROM tasks WHERE user_id <= ALL ( SELECT id FROM users WHERE id <= 2);

+----+-------+---------+

| no | task  | user_id |

+----+-------+---------+

| 11 | task1 |       1 |

| 14 | task5 |       1 |

+----+-------+---------+

 

 

* EXISTS

- 서브 쿼리의 결과값이 1개라도 있다면 true

- ANY와 비슷, ANY가 더 사용하기에 명확

- NOT EXISTS 가능

SELECT * FROM tasks WHERE EXISTS ( SELECT id FROM users WHERE users.id = tasks.user_id );

+----+-------+---------+

| no | task  | user_id |

+----+-------+---------+

| 11 | task1 |       1 |

| 12 | task2 |       2 |

| 14 | task5 |       1 |

+----+-------+---------+