hive三表关联实验

1.建表灌数

CREATE TABLE master_camp.mtj_A (
	user_id STRING,
	mark STRING
);

CREATE TABLE master_camp.mtj_B (
	user_id STRING,
	mark STRING
);

CREATE TABLE master_camp.mtj_C (
	user_id STRING,
	mark STRING
);

INSERT INTO master_camp.mtj_a VALUES
('sq001', '001'),
('sq001', '002'),
('sq001', '003'),
('sq004', '004'), 
('sq005', '005');

INSERT INTO master_camp.mtj_b VALUES
('sq002', '002'),
('sq003', '003'),
('sq004', '004');

INSERT INTO master_camp.mtj_c (user_id, mark) VALUES
('sq003', '003'),
('sq004', '004'),
('sq005', '005');

2.实验

2.1 三表连续join(123)

SELECT a.user_id, b.user_id, c.user_id , a.mark , b.mark , c.mark  FROM mtj_a a 
left join mtj_b b on a.user_id = b.user_id 
left join mtj_c c on b.user_id = c.user_id;

2.2 三表连续join(12 13)

SELECT a.user_id, b.user_id, c.user_id , a.mark , b.mark , c.mark  FROM mtj_a a 
left join mtj_b b on a.user_id = b.user_id 
left join mtj_c c on a.user_id = c.user_id;

3.小结论

三表关联，注意关联字段，不注意，会丢失想要留下的数据。