๐Ÿ“™SQL ๊ณต๋ถ€ 2์ผ์ฐจ

์ž„๋‚™ํ˜„ยท2022๋…„ 8์›” 4์ผ
0

SQL for DataBase

๋ชฉ๋ก ๋ณด๊ธฐ
2/4
post-thumbnail

ย ์ €๋ฒˆ ํฌ์ŠคํŒ…์—์„œ๋Š” Programmers์˜ 2๋‹จ๊ณ„ ๋ฌธ์ œ๋“ค์„ ํ’€์–ด๋ณด์•˜์Šต๋‹ˆ๋‹ค.

ย ๊ทธ ๋•Œ ๋ชจ๋“  ๋ฌธ์ œ๋ฅผ ํฌ์ŠคํŒ… ํ•˜์ง„ ์•Š์•˜์ง€๋งŒ ํ˜„์žฌ 2๋‹จ๊ณ„ ๋ฌธ์ œ๋“ค์€ ๋ชจ๋‘ ๋งˆ๋ฌด๋ฆฌ ํ•œ ์ƒํƒœ์ž…๋‹ˆ๋‹ค.

ย ์ด๋ฒˆ์—๋Š” 3๋‹จ๊ณ„ SQL๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.


Join

ย ์ด๋ฒˆ์— ๋ณผ ๋ฌธ์ œ๋Š” join์— ๊ด€๋ จ๋œ, ์œ„์— ๋ณด์ด๋Š” ์ฒซ ๋ฒˆ์งธ ๋ฌธ์ œ
์—†์–ด์ง„ ๊ธฐ๋ก ์ฐพ๊ธฐ ์ž…๋‹ˆ๋‹ค.


๊ฐ„๋‹จํžˆ ๋ฌธ์ œ ์š”์•ฝ์„ ํ•˜๋ฉด ANIMAL_OUTS์—๋Š” ์žˆ๋Š” ์ด๋ฆ„์ด์ง€๋งŒ ANIMAL_INS์—๋Š” ์—†๋Š” ์ด๋ฆ„์˜ ๋™๋ฌผ์„ ID ์™€ ์ด๋ฆ„์„ ID์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋ผ๋Š” ๋ฌธ์ œ์ž…๋‹ˆ๋‹ค.

๋จผ์ € ์ œ์ถœ ์ฝ”๋“œ๋ฅผ ๋ณด์—ฌ๋“œ๋ฆฌ๊ฒ ์Šต๋‹ˆ๋‹ค.

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_OUTS.ANIMAL_ID=ANIMAL_INS.ANIMAL_ID
WHERE ANIMAL_INS.NAME IS NULL AND
    NOT ANIMAL_OUTS.NAME IS NULL
ORDER BY ANIMAL_OUTS.ANIMAL_ID;

FROM

ย FROM ๋ฌธ์„ ๋ณด๋ฉด ANIMAL_OUTSํ…Œ์ด๋ธ”์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์˜ค๋Š”๊ฒƒ์„ ์•Œ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
๋ฌธ์ œ๋ฅผ ๋ณด๋ฉด ANIMAL_OUTS์— ์žˆ๋Š” ์ด๋ฆ„์„ ๊ฐ€์ ธ์™€์•ผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์šฐ์„ ์€ ANIMAL_OUTSํ…Œ์ด๋ธ”์„ ์„ ํƒํ•˜์˜€์Šต๋‹ˆ๋‹ค.

WHERE

ย WHERE๋ฌธ์„ ๋ณด๋ฉด ANIMAL_INS์—๋Š” ์—†๋Š” ์ด๋ฆ„์ด์–ด์•ผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ANIMAL_INS.NAME ๊ฐ€ NULL ๊ฐ’์ด์–ด์•ผ ํ•œ๋‹ค๋Š” ์กฐ๊ฑด์ด ์žˆ์œผ๋ฉฐ,
์ด ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋™์‹œ์— ANIMAL_OUTS์—๋Š” ์ด๋ฆ„์ด ์žˆ์–ด์•ผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์— ANIMAL_OUTS.NAME์€ NULL๊ฐ’์ด ๋˜๋ฉด ์•ˆ๋œ๋‹ค๋Š” ์กฐ๊ฑด๋„ ์ถ”๊ฐ€๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

SELECT

ย SELECT๋ฌธ์—์„œ๋Š” ์ด์ œ ANIMAL_OUTS.ANIMAL_ID์™€ ANIMAL_OUTS.NAME์„ ํ‘œ์‹œํ•ด ์ฃผ๋ฉฐ ๋ฌธ์ œ์—์„œ ์›ํ•˜๋Š” ์ปฌ๋Ÿผ๋งŒ ์„ ํƒํ•ด ์ฃผ์—ˆ์Šต๋‹ˆ๋‹ค.

ORDER BY

ย ORDER BY๋ฅผ ๋งˆ์ง€๋ง‰์œผ๋กœ, ๋ฌธ์ œ์—์„œ๋Š” ANIMAL_ID์ˆœ์œผ๋กœ ์กฐํšŒ๋ฅผ ํ•˜๋ผ ํ•˜์˜€๊ธฐ์—, FROM์—์„œ ์„ ํƒํ•œ ํ…Œ์ด๋ธ”์ธ ANIMAL_OUTS์˜ ANIMAL_ID๋กœ ์ •๋ ฌํ•ด ์ฃผ์—ˆ์Šต๋‹ˆ๋‹ค.


ย ์ด์ƒ์œผ๋กœ ์˜ค๋Š˜์˜ SQL ๋ฌธ์ œํ’€์ด๋ฅผ ๋งˆ์ณ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ๋‹ค์Œ์—๋Š” 3๋‹จ๊ณ„์˜ ๋‹ค๋ฅธ ์œ ํ˜•์„ ๋ณด๊ฑฐ๋‚˜ 4๋‹จ๊ณ„ ๋ฌธ์ œ๋กœ ํฌ์ŠคํŒ… ํ•ด ๋ณด๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ๊ฐ์‚ฌํ•ฉ๋‹ˆ๋‹ค.

profile
์ฝ”๋”ฉํ•˜๋Š” ์ˆ˜ํ•™๊ณผ

0๊ฐœ์˜ ๋Œ“๊ธ€