ÃֽŠ°Ô½Ã±Û(DB)
2022.05.02 / 16:08
[MySQL/MariaDB] JOIN¿¡¼ ON °ú WHEREÀÇ Â÷ÀÌÁ¡
ÄÚÄÚ·Î
Ãßõ ¼ö 138
MySQL JOIN¿¡¼ ON °ú WHEREÀÇ Â÷ÀÌÁ¡
what is the difference between ¡°ON¡± and ¡°WHERE¡± in a JOIN statement?
- SQLÀÇ JOIN¿¡¼ ON°ú WHEREÀÇ Â÷ÀÌÁ¡Àº JOINÇÏ´Â ¹üÀ§°¡ ´Ù¸£´Ù
¾Æ·¡ test1, test2 Å×À̺íÀ» ÂüÁ¶.
- ¿¹Á¦1
SELECT
*
FROM test1 AS a
LEFT JOIN test2 AS b
ON (a.aa = b.aa)
WHERE b.cc = 7;
- À§ÀÇ °æ¿ì´Â a¿Í b Å×À̺íÀÇ OUTER JOINÀ» ¼öÇàÇÑ ÈÄ¿¡ b.cc = 7ÀÎ µ¥ÀÌÅ͵éÀ» ÃßÃâ
µû¶ó¼ À§ÀÇ ÀÇ °á°ú´Â b.cc = 7ÀÎ µ¥ÀÌÅ͸¸ Á¸Àç
¿¹Á¦1 °á°ú :
- ¿¹Á¦2
SELECT
*
FROM test1 AS a
LEFT JOIN test2 AS b
ON (a.aa = b.aa AND b.cc = 7);
- À§ÀÇ °æ¿ì´Â (a Å×À̺í)°ú (b Å×À̺í Áß b.cc = 7ÀÎ °æ¿ì)¸¦ OUTER JOIN ÇÑ °á°ú°¡ ³ª¿Â´Ù.
µû¶ó¼ À§ÀÇ °á°ú´Â b.cc = 7ÀÌ ¾Æ´Ñ µ¥ÀÌÅ͵µ Á¸Àç
- ¿¹Á¦2 °á°ú :
ÇѸ¶µð·Î ON°ú WHEREÀÇ °æ¿ì´Â JOINÀ» ÇÒ ´ë»ó(¹üÀ§)ÀÌ ´Þ¶óÁø´Ù´Â °ÍÀÌ´Ù.
ÀÌ Á¡À» ÀÌ¿ëÇؼ LEFT OUTER JOINÀ¸·Î Â÷ÁýÇÕÀ» ±¸ÇöÇÒ ¼ö ÀÖ´Ù.
¿À¶óŬÀ̳ª MSSQL°ú °°Àº °æ¿ì´Â EXCEPT ȤÀº MINUS µîÀ» »ç¿ëÇÏ¸é µÇ°ÚÁö¸¸,
MySQLÀº ¹öÀü¿¡ µû¶ó Áö¿øÇÏ´Â °æ¿ìµµ ÀÖ°í ¾Æ´Ñ °æ¿ìµµ ÀÖ´Ù.
- test1 Å×À̺íÀÇ µ¥ÀÌÅÍ Áß test2 Å×ÀÌºí¿¡ ÀÖ´Â µ¥ÀÌÅ͸¦ Á¦¿ÜÇÏ°í °¡Á®¿À°í ½ÍÀº °æ¿ì.
SELECT
*
FROM test1 AS a
LEFT JOIN test2 AS b
ON (a.aa = b.aa)
WHERE b.aa IS NULL;
À§ÀÇ °æ¿ì JOINÇÏ´Â columnÀ» ±âÁØÀ¸·Î 1, 2´Â test2 Å×ÀÌºí¿¡µµ ÀÖÀ¸´Ï Á¦¿ÜÇÏ°í, 3, 6¸¸À» °¡Á®¿À°í ½ÍÀº °æ¿ìÀÌ´Ù.
- °á°ú :
- °á°ú :
WHEREÀý ÀÌÀü±îÁö ½ÇÇàÇßÀ» °æ¿ì
SELECT
*
FROM test1 AS a
LEFT JOIN test2 AS b
ON (a.aa = b.aa);
- ¿©±â¿¡¼ test2 Å×ÀÌºí¿¡ Á¸ÀçÇÏÁö ¾Ê¾Æ¼ test2 Å×À̺íÀÇ columnÀÌ nullÀÎ ºÎºÐ¸¸À» °¡Áö°í ¿À°Ô µÈ´Ù.
- WHEREÀýÀ» ´Þ¾ÆÁÖ¸é!! Â÷ÁýÇÕÀÌ µÈ´Ù´Â °ÍÀÌ´Ù.
- °á°ú :
Âü°í : https://blog.leocat.kr/notes/2017/07/28/sql-join-on-vs-where