join two query using SQL -

select test2.user_id,mytable1.mycol1 testingtable2 test2 lateral view explode(test2.purchased_item.product_id) mytable1 mycol1; 

i getting below output result using above query.

 user_id | mycol1 -------------+--------------- 1015826235 220003038067 1015826235 300003861266 1015826235 140002997245 1015826235 200002448035 

if compare above output query below table2 data, last line table2 data missing in above query output.

and second table2.

 buyer_id | item_id | created_time -------------+--------------------+-------------------------- 1015826235 220003038067 2012-06-21 07:57:39 1015826235 300003861266 2012-06-21 21:11:12 1015826235 140002997245 2012-06-14 20:10:16 1015826235 200002448035 2012-06-08 22:02:17 *1015826235* *260003553381* *2002-01-30 23:12:18* 

i need print last line using join, output should after join between query wrote above , table2 data.

*1015826235* *260003553381* *2002-01-30 23:12:18* 

so need join between above query wrote , table2 data , data not there in output above query data. suggestion?

just add mycol1 , item_id same thing , user_id , buyer_id same thing.

p.s- need use above query make join table2.

as @latr0dectus pointed out, looking except. in example can achieve using not in. following query give you:

all data table2 not in above table

select * table2 item_id not in ( select item_id abovetable ) 

update: well, if want join 2 tables way, can left join. note have add where t1.mycol1 null in order data table2 not in above table:

select * table2 t2 left join abovetable t1 on t2.item_id = t1.mycol1 t1.mycol1 null 

demo

update2: sql standard specifies tablereference1 join tablereference2 on ... described following diagram¹ :

the table reference can either table name table2 directly in query above, or joined table, or select statement select specific columns posted in column.

---

^{1 image sql queries mere mortals}