JB의 이모저모

MySQL Join 정리 본문

SQL/공부

MySQL Join 정리

J B 2023. 10. 2. 17:36

Join 이란?

join(조인) 또는 결합 구문은 한 데이터베이스 내의 여러 테이블의 레코드를 조합하여 하나의 열로 표현한 것이다. 따라서 조인은 테이블로서 저장되거나, 그 자체로 이용할 수 있는 결과 셋을 만들어 낸다. JOIN은 2개의 테이블에서 각각의 공통값을 이용함으로써 필드를 조합하는 수단이 된다.

 

 

sql에 대한 설명은 위의 그림이 잘 나타내고 있다. 

하지만 보고 이해하는거보다 직접 실습을 해보는편이 더 이해가 빠르다고 생각한다.

아래는 SQL문을 연습할 수 있는 사이트이다.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 

 

SQL Tryit Editor v1.6

WebSQL stores a Database locally, on the user's computer. Each user gets their own Database object. WebSQL is supported in Chrome, Safari, Opera, and Edge(79). If you use another browser you will still be able to use our Try SQL Editor, but a different ver

www.w3schools.com

 

실습을 위한 데이터

catergory 테이블과 product 테이블에 모두 categoryid가 존재하여 사용해주었다.

Cartegory 테이블

더보기
CategoryID CategoryName Description
1 Beverages Soft drinks, coffees, teas, beers, and ales
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings
3 Confections Desserts, candies, and sweet breads
4 Dairy Products Cheeses
5 Grains/Cereals Breads, crackers, pasta, and cereal
6 Meat/Poultry Prepared meats
7 Produce Dried fruit and bean curd
8 Seafood Seaweed and fish

Product 테이블

더보기
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 - 12 oz bottles 19
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10
4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35
6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25
7 Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. 30
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97
10 Ikura 4 8 12 - 200 ml jars 31
11 Queso Cabrales 5 4 1 kg pkg. 21
12 Queso Manchego La Pastora 5 4 10 - 500 g pkgs. 38
13 Konbu 6 8 2 kg box 6
14 Tofu 6 7 40 - 100 g pkgs. 23.25
15 Genen Shouyu 6 2 24 - 250 ml bottles 15.5
16 Pavlova 7 3 32 - 500 g boxes 17.45
17 Alice Mutton 7 6 20 - 1 kg tins 39
18 Carnarvon Tigers 7 8 16 kg pkg. 62.5
19 Teatime Chocolate Biscuits 8 3 10 boxes x 12 pieces 9.2
20 Sir Rodney's Marmalade 8 3 30 gift boxes 81
21 Sir Rodney's Scones 8 3 24 pkgs. x 4 pieces 10
22 Gustaf's Knäckebröd 9 5 24 - 500 g pkgs. 21
23 Tunnbröd 9 5 12 - 250 g pkgs. 9
24 Guaraná Fantástica 10 1 12 - 355 ml cans 4.5
25 NuNuCa Nuß-Nougat-Creme 11 3 20 - 450 g glasses 14
26 Gumbär Gummibärchen 11 3 100 - 250 g bags 31.23
27 Schoggi Schokolade 11 3 100 - 100 g pieces 43.9
28 Rössle Sauerkraut 12 7 25 - 825 g cans 45.6
29 Thüringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
30 Nord-Ost Matjeshering 13 8 10 - 200 g glasses 25.89
31 Gorgonzola Telino 14 4 12 - 100 g pkgs 12.5
32 Mascarpone Fabioli 14 4 24 - 200 g pkgs. 32
33 Geitost 15 4 500 g 2.5
34 Sasquatch Ale 16 1 24 - 12 oz bottles 14
35 Steeleye Stout 16 1 24 - 12 oz bottles 18
36 Inlagd Sill 17 8 24 - 250 g jars 19
37 Gravad lax 17 8 12 - 500 g pkgs. 26
38 Côte de Blaye 18 1 12 - 75 cl bottles 263.5
39 Chartreuse verte 18 1 750 cc per bottle 18
40 Boston Crab Meat 19 8 24 - 4 oz tins 18.4
41 Jack's New England Clam Chowder 19 8 12 - 12 oz cans 9.65
42 Singaporean Hokkien Fried Mee 20 5 32 - 1 kg pkgs. 14
43 Ipoh Coffee 20 1 16 - 500 g tins 46
44 Gula Malacca 20 2 20 - 2 kg bags 19.45
45 Røgede sild 21 8 1k pkg. 9.5
46 Spegesild 21 8 4 - 450 g glasses 12
47 Zaanse koeken 22 3 10 - 4 oz boxes 9.5
48 Chocolade 22 3 10 pkgs. 12.75
49 Maxilaku 23 3 24 - 50 g pkgs. 20
50 Valkoinen suklaa 23 3 12 - 100 g bars 16.25
51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53
52 Filo Mix 24 5 16 - 2 kg boxes 7
53 Perth Pasties 24 6 48 pieces 32.8
54 Tourtière 25 6 16 pies 7.45
55 Pâté chinois 25 6 24 boxes x 2 pies 24
56 Gnocchi di nonna Alice 26 5 24 - 250 g pkgs. 38
57 Ravioli Angelo 26 5 24 - 250 g pkgs. 19.5
58 Escargots de Bourgogne 27 8 24 pieces 13.25
59 Raclette Courdavault 28 4 5 kg pkg. 55
60 Camembert Pierrot 28 4 15 - 300 g rounds 34
61 Sirop d'érable 29 2 24 - 500 ml bottles 28.5
62 Tarte au sucre 29 3 48 pies 49.3
63 Vegie-spread 7 2 15 - 625 g jars 43.9
64 Wimmers gute Semmelknödel 12 5 20 bags x 4 pieces 33.25
65 Louisiana Fiery Hot Pepper Sauce 2 2 32 - 8 oz bottles 21.05
66 Louisiana Hot Spiced Okra 2 2 24 - 8 oz jars 17
67 Laughing Lumberjack Lager 16 1 24 - 12 oz bottles 14
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 12.5
69 Gudbrandsdalsost 15 4 10 kg pkg. 36
70 Outback Lager 7 1 24 - 355 ml bottles 15
71 Fløtemysost 15 4 10 - 500 g pkgs. 21.5
72 Mozzarella di Giovanni 14 4 24 - 200 g pkgs. 34.8
73 Röd Kaviar 17 8 24 - 150 g jars 15
74 Longlife Tofu 4 7 5 kg pkg. 10
75 Rhönbräu Klosterbier 12 1 24 - 0.5 l bottles 7.75
76 Lakkalikööri 23 1 500 ml 18
77 Original Frankfurter grüne Soße 12 2 12 boxes 13

 

Join(Inner Join) - 내부 조인

결합 조건을 만족하는 데이터만 선택해 두 테이블을 결합한다.

 

C테이블의 CategoryID 와 P테이블의 CategoryID 가 같은거를 조인한다,

SELECT * FROM Categories AS C
JOIN Products AS P
ON C.CategoryID = P.CategoryID

결과

더보기
CategoryID CategoryName Description ProductID ProductName SupplierID Unit Price
1 Beverages Soft drinks, coffees, teas, beers, and ales 1 Chais 1 10 boxes x 20 bags 18
1 Beverages Soft drinks, coffees, teas, beers, and ales 2 Chang 1 24 - 12 oz bottles 19
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 3 Aniseed Syrup 1 12 - 550 ml bottles 10
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 4 Chef Anton's Cajun Seasoning 2 48 - 6 oz jars 22
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 5 Chef Anton's Gumbo Mix 2 36 boxes 21.35
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 6 Grandma's Boysenberry Spread 3 12 - 8 oz jars 25
7 Produce Dried fruit and bean curd 7 Uncle Bob's Organic Dried Pears 3 12 - 1 lb pkgs. 30
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 8 Northwoods Cranberry Sauce 3 12 - 12 oz jars 40
6 Meat/Poultry Prepared meats 9 Mishi Kobe Niku 4 18 - 500 g pkgs. 97
8 Seafood Seaweed and fish 10 Ikura 4 12 - 200 ml jars 31
4 Dairy Products Cheeses 11 Queso Cabrales 5 1 kg pkg. 21
4 Dairy Products Cheeses 12 Queso Manchego La Pastora 5 10 - 500 g pkgs. 38
8 Seafood Seaweed and fish 13 Konbu 6 2 kg box 6
7 Produce Dried fruit and bean curd 14 Tofu 6 40 - 100 g pkgs. 23.25
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 15 Genen Shouyu 6 24 - 250 ml bottles 15.5
3 Confections Desserts, candies, and sweet breads 16 Pavlova 7 32 - 500 g boxes 17.45
6 Meat/Poultry Prepared meats 17 Alice Mutton 7 20 - 1 kg tins 39
8 Seafood Seaweed and fish 18 Carnarvon Tigers 7 16 kg pkg. 62.5
3 Confections Desserts, candies, and sweet breads 19 Teatime Chocolate Biscuits 8 10 boxes x 12 pieces 9.2
3 Confections Desserts, candies, and sweet breads 20 Sir Rodney's Marmalade 8 30 gift boxes 81
3 Confections Desserts, candies, and sweet breads 21 Sir Rodney's Scones 8 24 pkgs. x 4 pieces 10
5 Grains/Cereals Breads, crackers, pasta, and cereal 22 Gustaf's Knäckebröd 9 24 - 500 g pkgs. 21
5 Grains/Cereals Breads, crackers, pasta, and cereal 23 Tunnbröd 9 12 - 250 g pkgs. 9
1 Beverages Soft drinks, coffees, teas, beers, and ales 24 Guaraná Fantástica 10 12 - 355 ml cans 4.5
3 Confections Desserts, candies, and sweet breads 25 NuNuCa Nuß-Nougat-Creme 11 20 - 450 g glasses 14
3 Confections Desserts, candies, and sweet breads 26 Gumbär Gummibärchen 11 100 - 250 g bags 31.23
3 Confections Desserts, candies, and sweet breads 27 Schoggi Schokolade 11 100 - 100 g pieces 43.9
7 Produce Dried fruit and bean curd 28 Rössle Sauerkraut 12 25 - 825 g cans 45.6
6 Meat/Poultry Prepared meats 29 Thüringer Rostbratwurst 12 50 bags x 30 sausgs. 123.79
8 Seafood Seaweed and fish 30 Nord-Ost Matjeshering 13 10 - 200 g glasses 25.89
4 Dairy Products Cheeses 31 Gorgonzola Telino 14 12 - 100 g pkgs 12.5
4 Dairy Products Cheeses 32 Mascarpone Fabioli 14 24 - 200 g pkgs. 32
4 Dairy Products Cheeses 33 Geitost 15 500 g 2.5
1 Beverages Soft drinks, coffees, teas, beers, and ales 34 Sasquatch Ale 16 24 - 12 oz bottles 14
1 Beverages Soft drinks, coffees, teas, beers, and ales 35 Steeleye Stout 16 24 - 12 oz bottles 18
8 Seafood Seaweed and fish 36 Inlagd Sill 17 24 - 250 g jars 19
8 Seafood Seaweed and fish 37 Gravad lax 17 12 - 500 g pkgs. 26
1 Beverages Soft drinks, coffees, teas, beers, and ales 38 Côte de Blaye 18 12 - 75 cl bottles 263.5
1 Beverages Soft drinks, coffees, teas, beers, and ales 39 Chartreuse verte 18 750 cc per bottle 18
8 Seafood Seaweed and fish 40 Boston Crab Meat 19 24 - 4 oz tins 18.4
8 Seafood Seaweed and fish 41 Jack's New England Clam Chowder 19 12 - 12 oz cans 9.65
5 Grains/Cereals Breads, crackers, pasta, and cereal 42 Singaporean Hokkien Fried Mee 20 32 - 1 kg pkgs. 14
1 Beverages Soft drinks, coffees, teas, beers, and ales 43 Ipoh Coffee 20 16 - 500 g tins 46
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 44 Gula Malacca 20 20 - 2 kg bags 19.45
8 Seafood Seaweed and fish 45 Røgede sild 21 1k pkg. 9.5
8 Seafood Seaweed and fish 46 Spegesild 21 4 - 450 g glasses 12
3 Confections Desserts, candies, and sweet breads 47 Zaanse koeken 22 10 - 4 oz boxes 9.5
3 Confections Desserts, candies, and sweet breads 48 Chocolade 22 10 pkgs. 12.75
3 Confections Desserts, candies, and sweet breads 49 Maxilaku 23 24 - 50 g pkgs. 20
3 Confections Desserts, candies, and sweet breads 50 Valkoinen suklaa 23 12 - 100 g bars 16.25
7 Produce Dried fruit and bean curd 51 Manjimup Dried Apples 24 50 - 300 g pkgs. 53
5 Grains/Cereals Breads, crackers, pasta, and cereal 52 Filo Mix 24 16 - 2 kg boxes 7
6 Meat/Poultry Prepared meats 53 Perth Pasties 24 48 pieces 32.8
6 Meat/Poultry Prepared meats 54 Tourtière 25 16 pies 7.45
6 Meat/Poultry Prepared meats 55 Pâté chinois 25 24 boxes x 2 pies 24
5 Grains/Cereals Breads, crackers, pasta, and cereal 56 Gnocchi di nonna Alice 26 24 - 250 g pkgs. 38
5 Grains/Cereals Breads, crackers, pasta, and cereal 57 Ravioli Angelo 26 24 - 250 g pkgs. 19.5
8 Seafood Seaweed and fish 58 Escargots de Bourgogne 27 24 pieces 13.25
4 Dairy Products Cheeses 59 Raclette Courdavault 28 5 kg pkg. 55
4 Dairy Products Cheeses 60 Camembert Pierrot 28 15 - 300 g rounds 34
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 61 Sirop d'érable 29 24 - 500 ml bottles 28.5
3 Confections Desserts, candies, and sweet breads 62 Tarte au sucre 29 48 pies 49.3
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 63 Vegie-spread 7 15 - 625 g jars 43.9
5 Grains/Cereals Breads, crackers, pasta, and cereal 64 Wimmers gute Semmelknödel 12 20 bags x 4 pieces 33.25
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 65 Louisiana Fiery Hot Pepper Sauce 2 32 - 8 oz bottles 21.05
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 66 Louisiana Hot Spiced Okra 2 24 - 8 oz jars 17
1 Beverages Soft drinks, coffees, teas, beers, and ales 67 Laughing Lumberjack Lager 16 24 - 12 oz bottles 14
3 Confections Desserts, candies, and sweet breads 68 Scottish Longbreads 8 10 boxes x 8 pieces 12.5
4 Dairy Products Cheeses 69 Gudbrandsdalsost 15 10 kg pkg. 36
1 Beverages Soft drinks, coffees, teas, beers, and ales 70 Outback Lager 7 24 - 355 ml bottles 15
4 Dairy Products Cheeses 71 Fløtemysost 15 10 - 500 g pkgs. 21.5
4 Dairy Products Cheeses 72 Mozzarella di Giovanni 14 24 - 200 g pkgs. 34.8
8 Seafood Seaweed and fish 73 Röd Kaviar 17 24 - 150 g jars 15
7 Produce Dried fruit and bean curd 74 Longlife Tofu 4 5 kg pkg. 10
1 Beverages Soft drinks, coffees, teas, beers, and ales 75 Rhönbräu Klosterbier 12 24 - 0.5 l bottles 7.75
1 Beverages Soft drinks, coffees, teas, beers, and ales 76 Lakkalikööri 23 500 ml 18
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 77 Original Frankfurter grüne Soße 12 12 boxes 13

 

Outer Join

Outer join은 결합 조건을 만족하지 않아도 그 종류에 따라 특정 테이블의 데이터를 모두 선택해 두 테이블을 결합한다.

    • Left Join : 첫 번째 테이블의 데이터를 모두 선택한 후, 두 번째 테이블의 데이터를 결합 조건에 따라 매칭한다.매칭되는 데이터가 없는 경우 그 값을 null로 표시한다.
    • Right Join : 두 번째 테이블의 데이터를 모두 선택한 후, 첫 번째 테이블의 데이터를 결합 조건에 따라 매칭한다.매칭되는 데이터가 없는 경우 그 값을 null로 표시한다.
    • Full Outer Join: 두 테이블의 데이터를 모두 선택하고, 결합 조건에 따라 데이터를 매칭한다. 매칭되는 데이터가 없는 경우 그 값을 null로 표시한다. MySQL에는 Full Outer Join이 없기 때문에 Left Join과 Right Join의 합집합을 구해서 만든다.

 

더 쉬운 예제를 확인하기 위해서 아래 블로그를 참고하였다.

https://cceeun.tistory.com/189

 

[Oracle] SQL 문법 JOIN 조인 예제 및 정리 (1) - INNER JOIN | OUTER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN | 조인절

실무에서 필수로 쓰이는 JOIN 절에 대해서 정리해보겠다. 이번 포스팅에서는 INNER 조인 OUTER 조인에 대해서 간략하게 알아보자.. INNER JOIN 이란? LEFT OUTER JOIN 이란? .. 매번 JOIN절은 공부할때마다 이

cceeun.tistory.com

TABLE : FOOD_A

ID FOODNAME
1 돈까스
2 삼겹살
3 초밥
4 곱창전골

TABLE : FOOD_B

ID FOODNAME
1 초밥
2 돈까스
3 칼국수
4 햄버거

Left Join

A LEFT JOIN B 일 경우, A는 모두 다 추출하고 B에 있는 A에 존재하는 행들만 추출한다. (A가 기준이 됨!!!!)

A에 있는 B값만 가져온다. A에 있는데 B에는 없는 경우 NULL 처리

SQL문

SELECT * FROM FOOD_A AS A
LEFT JOIN FOOD_B AS B
ON A.FOODNAME = B.FOODNAME

결과

A.ID A.FOODNAME B.ID B.FOODNAME
1 돈까스 2 돈까스
2 삼겹살 NULL NULL
3 초밥 1 초밥
4 곱창전골 NULL NULL

 

Left Join - (Left Only)

A의 전체를 추출하되, 만일 B와 매핑 되는 것이 있다면 그거는 제외하고 추출하는 것

오로지 겹치는거 없이 홀로 있는 A의 데이터만 뽑고자 할 때

SQL문

SELECT * FROM FOOD_A AS A
LEFT JOIN FOOD_B AS B
ON A.FOODNAME = B.FOODNAME
WHERE B.ID IS NULL

결과

A.ID A.FOODNAME B.ID B.FOODNAME
2 삼겹살 NULL NULL
4 곱창전골 NULL NULL

 

Right Join

A RIGHT JOIN B 의 경우 B를 기준으로 A와 조인한다.

SQL 문

SELECT * FROM FOOD_A AS A
RIGHT JOIN FOOD_B AS B
ON A.FOODNAME = B.FOODNAME

결과

A.ID A.FOODNAME B.ID B.FOODNAME
1 돈까스 2 돈까스
NULL NULL 3 칼국수
3 초밥 1 초밥
NULL NULL 4 햄버거

 

Right Join - (Right Only)

A RIGHT JOIN B의 경우 기준이 되는 B의 모든 데이터 중 A에 없는 데이터만 추출한다.

SQL문

SELECT * FROM FOOD_A AS A
RIGHT JOIN FOOD_B AS B
ON A.FOODNAME = B.FOODNAME
WHERE A.ID IS NULL

결과

A.ID A.FOODNAME B.ID B.FOODNAME
NULL NULL 3 칼국수
NULL NULL 4 햄버거

'SQL > 공부' 카테고리의 다른 글

MySQL UNION vs UNION ALL  (1) 2023.10.02
MySQL 문법 정리  (0) 2023.09.30
'SQL/공부' Related Articles more