[SQL] 조건에 조건 더하기(서브 쿼리)

서브 쿼리

하나의 쿼리 내 포함된 또 하나의 쿼리 의미(괄호 안에 있어야 함)

SELECT,FROM,WHERE, HAVING, ORDER BY, INSERT, UPDATE, DELETE 문에서 사용 가능

세미 콜론 안 붙여도 됨

SELECT 절 서브 쿼리

반드시 결과값이 하나의 값 이어야 함

FROM 절 서브쿼리

반드시 결과값이 하나의 테이블, 서브 쿼리로 만든 테이블은 반드시 별명을 가져야 함

WHERE 절 서브쿼리

반드시 결과값이 하나의 칼럼이여야 함(EXISTS 제외)
-> 하나의 칼럼에는 여러 개의 값 존재 가능

코드 예시

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20)
);
INSERT INTO mypokemon (number, name)
VALUES (10, 'caterpie'),
(25, 'pikachu'),
(26, 'raichu'),
(133, 'eevee'),
(152, 'chikoirita');
CREATE TABLE ability (
number INT,
type VARCHAR(10),
height FLOAT,
weight FLOAT,
attack INT,
defense INT,
speed int
);
INSERT INTO ability (number, type, height, weight, attack, defense, speed)
VALUES (10, 'bug', 0.3, 2.9, 30, 35, 45),
(25, 'electric', 0.4, 6, 55, 40, 90),
(26, 'electric', 0.8, 30, 90, 55, 110),
(133, 'normal', 0.3, 6.5, 55, 50, 55),
(152, 'grass', 0.9, 6.4, 49, 65, 45);

SELECT number
FROM ability
WHERE weight >= ALL (SELECT weight FROM ability);

// 위와 동일 코드
SELECT number
FROM ability
WHERE weight = (SELECT MAX(weight) FROM ability);

SELECT number
FROM ability
WHERE speed < ANY (SELECT attack FROM ability WHERE type = 'electric'); 

SELECT name
FROM mypokemon
WHERE EXISTS(SELECT * FROM ability WHERE attack > defense); 

SELECT name,
(SELECT height FROM ability WHERE number = 133) AS height,
(SELECT weight FROM ability WHERE number = 133) AS weight
FROM mypokemon
WHERE number = 133;

SELECT number, speed
FROM 
	(SELECT number, speed, RANK() OVER(ORDER BY speed DESC) AS speed_rank 
    FROM ability ) AS Atable
WHERE speed_rank = 2;

SELECT name
FROM
	(SELECT name, number as number1, 
    (SELECT number FROM ability WHERE defense > ALL
    (SELECT defense FROM ability WHERE type = 'electric')) as number2
    FROM mypokemon ) AS Atable
WHERE number2 IN(number1);