MySQLその他のデフォルト構文

CASE

CASE
	WHEN 조건
	THEN '반환 값'
	WHEN 조건
	THEN '반환 값'
	ELSE 'WHEN 조건에 해당 안되는 경우 반환 값'
END

サンプルケース

SELECT
	idx,
    CASE
		WHEN type = '1'
		THEN '의사'
		WHEN type = '2'
		THEN '장군'
		WHEN type = '3'
		THEN '왕'
		ELSE '일반인'
	END AS hero_type,
	name
FROM hero_collection;

結果

SUBQUERY

SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6

IN, NOT IN

SELECT *
FROM customers
WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)

EXISTS

SELECT EmployeeId
FROM employees e
WHERE EXISTS (
	SELECT 1
	FROM customers c
	WHERE c.SupportRepId = e.EmployeeId
	)
ORDER BY EmployeeId

FROM

SELECT *
FROM (
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)