MySQL) JOIN#1. 동등조인, equi join

SQL INNER JOIN Keyword

www.w3schools.com/sql/sql_join_inner.asp

SQL INNER JOIN Keyword

---

mysql 접근

C:\Users\W>docker ps -a
CONTAINER ID   IMAGE     COMMAND                  CREATED       STATUS       PORTS                                                  NAMES
f668fc6c47e6   mysql     "docker-entrypoint.s…"   3 hours ago   Up 2 hours   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql1

C:\Users\W>docker exec -it mysql1 bash
root@f668fc6c47e6:/#
root@f668fc6c47e6:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

---

부서테이블과 사원테이블 생성

실행할 SQL 메모

 

DROP DATABASE studydb;

CREATE DATABASE studydb;

USE studydb;

 

CREATE TABLE dept(

no INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20)

);

 

INSERT INTO dept(name) VALUES('A');

INSERT INTO dept(name) VALUES('B');

INSERT INTO dept(name) VALUES('C');

 

CREATE TABLE employee(

no INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20),

dept_no INT

#,FOREIGN KEY (employee) REFERENCES dept(no)

);

 

INSERT INTO employee(name, dept_no) VALUES('ga', 2);

INSERT INTO employee(name, dept_no) VALUES('na', 3);

INSERT INTO employee(name, dept_no) VALUES('da', 3);

INSERT INTO employee(name, dept_no) VALUES('la', 2);

INSERT INTO employee(name, dept_no) VALUES('ma', 2);

 

---

실행결과

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE DATABASE studydb;
Query OK, 1 row affected (0.25 sec)

mysql> USE studydb;
Database changed
mysql> CREATE TABLE dept(
    -> no INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (2.44 sec)

mysql> INSERT INTO dept(name) VALUES('A');
Query OK, 1 row affected (0.26 sec)

mysql> INSERT INTO dept(name) VALUES('B');
Query OK, 1 row affected (0.34 sec)

mysql> INSERT INTO dept(name) VALUES('C');
Query OK, 1 row affected (0.31 sec)

mysql> CREATE TABLE employee(
    -> no INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> dept_no INT
    -> );
Query OK, 0 rows affected (1.87 sec)

mysql> INSERT INTO employee(name, dept_no) VALUES('ga',2);
Query OK, 1 row affected (0.21 sec)

mysql> INSERT INTO employee(name, dept_no) VALUES('na',3);
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO employee(name, dept_no) VALUES('da',3);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO employee(name, dept_no) VALUES('la', 2);
Query OK, 1 row affected (0.23 sec)

mysql> INSERT INTO employee*name, dept_no) VALUES('ma', 2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*name, dept_no) VALUES('ma', 2)' at line 1
mysql> INSERT INTO employee(name, dept_no) VALUES('ma', 2);
Query OK, 1 row affected (0.47 sec)

mysql> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| dept              |
| employee          |
+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM dept;
+----+------+
| no | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM employee;
+----+------+---------+
| no | name | dept_no |
+----+------+---------+
|  1 | ga   |       2 |
|  2 | na   |       3 |
|  3 | da   |       3 |
|  4 | la   |       2 |
|  5 | ma   |       2 |
+----+------+---------+
5 rows in set (0.00 sec)

---

사원번호와 이름에따른 부서명 출력

: 동등조인

 

실행할 SQL 메모

 

SELECT employee.no, employee.name, dept.name FROM dept, employee

WHERE employee.dept_no=dept.no;

->

SELECT e.no, e.name, d.name FROM dept d, employee e

WHERE e.dept_no = d.no;

 

풀로 쓰기 힘들때는 약자사용 가능.

---

 

mysql> SELECT employee.no, employee.name, dept.name FROM dept, employee
    -> WHERE employee.dept_no=dept.no;
+----+------+------+
| no | name | name |
+----+------+------+
|  1 | ga   | B    |
|  2 | na   | C    |
|  3 | da   | C    |
|  4 | la   | B    |
|  5 | ma   | B    |
+----+------+------+
5 rows in set (0.00 sec)

mysql> SELECT e.no, e.name, d.name FROM dept d, employee e
    -> WHERE e.dept_no = d.no;
+----+------+------+
| no | name | name |
+----+------+------+
|  1 | ga   | B    |
|  2 | na   | C    |
|  3 | da   | C    |
|  4 | la   | B    |
|  5 | ma   | B    |
+----+------+------+
5 rows in set (0.00 sec)

 

---

 

---