[Oracle] join

주문테이블 생성

create table tb_order (
    ord_no varchar2(10),
    ord_userid varchar2(20),
    ord_product varchar2(50) not null,
    ord_count number(4),
    ord_price number(7),
    ord_regdate date default sysdate,
    constraint pk_no primary key(ord_no),
    constraint fk_userid foreign key(ord_userid) references tb_member(mem_userid)
);

 

데이터 삽입

insert into tb_order values ('00000001', 'apple', '삼성노트북', 1, 1000000, sysdate);
insert into tb_order values ('00000002', 'banana', '맥북프로', 1, 1400000, sysdate);
insert into tb_order values ('00000003', 'orange', '엘지그램', 1, 1200000, sysdate);
insert into tb_order values ('00000004', 'banana', '삼성에어컨', 1, 800000, sysdate);
insert into tb_order values ('00000005', 'apple', '엘지모니터', 1, 300000, sysdate);

 

조인

select 컬럼명... from 테이블명1 [left, right, inner] join 테이블명2 on 테이블명1.공통된컬럼(pk) = 테이블명2.공통된컬럼(fk);

 

/*
    출력
    구매자 아이디, 구매자 이름, 구매자 성별, 구입한 물건, 구입한 가격
*/

select mem_userid, mem_name, mem_gender, ord_product, ord_price from tb_member inner join tb_order on tb_member.mem_userid = tb_order.ord_userid;