[MySQL] JOIN : CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN / UNION

28 Nov 2021 - 1 minute read

create table customers(
	customerid		char(10)	primary key,
    name			char(10)	not null,
    email			char(20)
);

create table orders(
	num				int			primary key	auto_increment,
    orderdate		char(15)	not null,
    customerid		char(10),
    amount			int			not null
);

insert into customers 
values
('A123', 'Alice', 'aceee@email.com'),
('j_mes_id', 'James', 'j_mes@email.com'),
('popo', 'Pauline', 'pol2@email.com');

insert into orders
(orderdate, customerid, amount)
values
('21-10-25', 'j_mes_id', 56000),
('21-11-17', 'popo', 73200),
('21-11-27', 'j_mes_id', 34900);

JOIN : 테이블의 결합

---

CROSS JOIN

---

• 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능

• SELECT * FROM table_name1, table_name2; 
  

• ex)

  SELECT * FROM customers, orders;
  

  

INNER JOIN

---

• 기준 테이블과 조인 테이블 모두에 데이터가 존재해야 조회됨

• IMPLICIT INNER JOIN

  • SELECT * FROM table_name1, table_name2
         WHERE table_name1.column_name = table_name2.column_name;
    

  • ex)

      select * from customers, orders where customers.customerid = orders.customerid order by num;
    

    

• EXPLICIT INNER JOIN

  • SELECT * FROM table_name1
    JOIN table_name2
    ON table_name1.column_name = table_name2.column_name;
    

  • ex)

      select * from customers
      join orders
      on customers.customerid = orders.customerid;
    

    

LEFT JOIN

---

• 테이블1 전체 + 테이블1, 테이블2의 합집합

• 일치하는 값이 없더라도 왼쪽 값(테이블1의 값) 모두 조회, 데이터가 없으면 NULL

• SELECT * FROM table_name1
  LEFT JOIN table_name2
  ON table_name1.column_name = table_name2.column_name;
  

• ex)

    SELECT * FROM customers
    LEFT JOIN orders
    ON customers.customerid = orders.customerid order by num;
  

  

RIGHT JOIN

---

• 테이블1, 테이블2의 합집합 + 테이블2 전체

• 일치하는 값이 없더라도 오른쪽 값(테이블2의 값) 모두 조회, 데이터가 없으면 NULL

• SELECT * FROM table_name1
  RIGHT JOIN table_name2
  ON table_name1.column_name = table_name2.column_name;
  

• ex)

    insert into orders
    (orderdate, customerid, amount)
    values
    ('21-11-28', 'mylll', 46300);
  
    SELECT * FROM customers
    RIGHT JOIN orders
    ON customers.customerid = orders.customerid order by num;
  

  

UNION

---

• JOIN : 가로 결합 방식 / UNION : 세로 결합 방식

• SELECT column_name1 FROM table_name1
  UNION (ALL)
  SELECT column_name2 FROM table_name2
  

  • 두 개 테이블의 조회할 컬럼 개수는 일치해야 함

  • UNION ALL : 중복된 데이터 모두 표시

• ex)

    create table schoolA(
        name		char(10),
        subject	char(10),
        score		int
    );
  
    insert into schoolA
    values
    ('최강', '국어', 86),
    ('강새벽', '영어', 78);
  
  
    create table schoolB(
        stuName		char(10),
        stuSubject	char(10),
        stuScore	int
    );
  
    insert into schoolB
    values
    ('강새벽', '영어', 78),
    ('박하늘', '영어', 89),
    ('이바다', '수학', 100);
  
    # union
    select name, score from schoolA
    union
    select stuName, stuScore from schoolB;
  
    # union all
    select name, score from schoolA
    union all
    select stuName, stuScore from schoolB;