join two query using SQL

Question

SELECT test2.user_id,myTable1.myCol1 FROM testingtable2 test2 LATERAL VIEW 
explode(test2.purchased_item.product_id) myTable1 AS myCol1;

I am getting the below output result using the above query.

  USER_ID    |     myCol1  
-------------+---------------
1015826235      220003038067

1015826235      300003861266

1015826235      140002997245

1015826235      200002448035

If you compare the above output from the query with the below Table2 data, then the last line from Table2 data is missing in the above query output.

And this is the 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 to print the last line basically using the JOIN, so the output should be like this after the JOIN between the query I wrote above and Table2 data.

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

So I need to do the JOIN between the above query I wrote and Table2 data and get all the data that is not there in the output from the above query data. Any suggestion?

Just to add myCol1 and ITEM_ID are same thing and USER_ID and BUYER_ID are same thing.

P.S- I need to use my above query to make the JOIN with Table2.

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

3

As @latr0dectus pointed out, you are looking for the EXCEPT. In your example you can achieve this by using NOT IN. The following query will give you:

All the data from Table2 that is not in the above table

SELECT *
FROM Table2
WHERE ITEM_ID NOT IN
(
    SELECT ITEM_ID
    FROM AboveTable
)

Update: Well, if you want to JOIN the two tables any way, then you can do this with LEFT JOIN. Note that you have to add WHERE t1.myCol1 IS NULL in order to get only all the data from Table2 that is not in the above table:

SELECT * 
FROM Table2 t2
LEFT JOIN AboveTable t1 ON t2.ITEM_ID = t1.myCol1
WHERE t1.myCol1 IS NULL

DEMO

Update2: The SQL standard specifies that TableReference1 JOIN TableReference2 ON ... as described by the following diagram¹ :

enter image description here

the Table Reference can be either a table name like Table2 directly like in my query above, or a Joined Table, or a SELECT statement that select only some specific columns as you posted in your column.

^{¹ Image From SQL Queries for Mere Mortals}

edited Jan 18 '21 at 12:30

Community

1
1

answered Jul 07 '12 at 07:24

Mahmoud Gamal

78,257
17
139
164

Thanks for commenting but I don't just need ITEM_ID, I need the data like this, the last line in full `*1015826235* *260003553381* *2002-01-30 23:12:18*` – arsenal Jul 07 '12 at 07:35
@rjchar, `SELECT *` will give you the full line, e.g., `BUYER_ID, ITEM_ID, CREATED_TIME` from the second table. Isn't this what you need? – Mahmoud Gamal Jul 07 '12 at 07:38
Actually I understand what your point is, but my question is I need to use the query that I wrote above for JOINING with Table2, I cannot do it simply like this as the query that I wrote I need to use it somehow. So that is the reason I was asking if I can use my query to JOIN with Table2. – arsenal Jul 07 '12 at 07:47
Thanks Mahmoud for taking so much pain to explain me as I am new to these stuff, but the data that I am getting from the query is not a table actually, that data I am getting from that query only, so that means in your example AboveTable is the data that I am getting from that query. So I need to use that query somehow to JOIN with table2. So if I do it like this below then it will be good? – arsenal Jul 07 '12 at 08:26
`SELECT * from (SELECT test2.user_id,myTable1.myCol1 FROM testingtable2 test2 LATERAL VIEW explode(test2.purchased_item.product_id) myTable1 AS myCol1) myTable1 LEFT OUTER JOIN Table2 ON (Table2.item_id = myTable1.myCol1) where myTable1.myCol1 IS NULL ;` – arsenal Jul 07 '12 at 08:27
@rjchar, There is no pain at all for me, that's why we love SO. So please feel free to ask for any more details and explanations. – Mahmoud Gamal Jul 07 '12 at 08:35
@rjchar, You can ofcorse `JOIN` the two tables the way you posted. Or select a specific data from other tables if you want so instead of `From tablName` the `tableName` can be a table name like `Table2` or any select statement that select specific columns or columns that comes from more tables, all valid SQL. – Mahmoud Gamal Jul 07 '12 at 08:36
Just an update, I ran the above query that I specified in the comment and it returned zero record, why is it so? In this case it should be LEFT OUTER JOIN or RIGHT OUTER JOIN as myTable1 will be compared with Table2 in this case, So I think it should be RIGHT OUTER JOIN? Any suggestions? – arsenal Jul 07 '12 at 08:46
@rjchar, You should choose the type of `JOIN` the data you want to select form the two tables, `LEFT JOIN` will give you all the records form the first table, the left one even if there is no match for it in the second table as in my query the `t1.myCol1` is equal to `NULL` for those rows that has no match for `ITEM_ID` in the second table. `RIGHT JOIN` will make the same thing but for the right table or the second table. – Mahmoud Gamal Jul 07 '12 at 08:54
@rjchar, You should read more about the difference between the `JOIN` different types, read this excellent article for Jiff attwod: [A Visual Explanation of SQL Joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) and this article from Wikipedia: [Join (SQL)](http://en.wikipedia.org/wiki/Join_%28SQL%29). – Mahmoud Gamal Jul 07 '12 at 08:54
Thanks for all your help. I will definitely checkout those links for sure. I will be really glad if you can give some pointers on this question, it is also related to SQL and I haven't got any reply yet. [http://stackoverflow.com/questions/11336950/joining-two-tables-in-hive-using-hiveqlhadoop](http://stackoverflow.com/questions/11336950/joining-two-tables-in-hive-using-hiveqlhadoop) – arsenal Jul 07 '12 at 09:00
@rjchar: I think the query you were asking about in the other comment is almost correct, only you need to swap the sides of the left join. It should be `… FROM Table2 LEFT OUTER JOIN (SELECT … FROM your-lateral-view-thing) MyTable1 ON …`. And you only need data from the left side, so it should probably begin like this: `SELECT Table2.* FROM …` – Andriy M Jul 07 '12 at 09:04
@rjchar, Try this for the other question that you pointed to and tell me how it didn't work the way you want. It should work properly: `SELECT t1.buyer_id, t1.item_id, t1.created_time, UNIX_TIMESTAMP(t1.created_time) FROM Table1 t1 LEFT JOIN ( SELECT user_id,prodID FROM Table2 test2 LATERAL VIEW explode( test2.purchased_item.product_id ) prodTable AS prodID ) prodTable ON t1.buyer_id = prodTable.user_id;` – Mahmoud Gamal Jul 07 '12 at 09:12
Thanks Mahmoud, Let me try that and will update you on that. Thanks a lot for all your help. Really Appreciated. – arsenal Jul 07 '12 at 09:17

score 1 · Answer 2 · edited Jul 07 '12 at 07:26

1

If you are using SQL Server you can use EXCEPT. If Oracle you can use MINUS. It basically will return the different between query 1 and query 2.

In your case do your second query, then EXCEPT, followed by the first query.

edited Jul 07 '12 at 07:26

Mahmoud Gamal

78,257
17
139
164

answered Jul 07 '12 at 06:17

Michael Christensen

1,768
1
13
11

Can you show me example basis on my sql example. Then I will be able to understand more. But there is no way I can use JOIN here? – arsenal Jul 07 '12 at 06:19
I am on to NOSQL environment. So that is the reason I was asking if we can do this anyhow using JOIN as JOIN syntax will work in NOSQL environment, as I am currently working wtih Hive – arsenal Jul 07 '12 at 07:04
2

that is certainly relevant to mention, especially since your tag says SQL :) – Michael Christensen Jul 07 '12 at 07:06
Yes, It seems that we can use sql syntax in Hive also. so that is the reason I was asking this If we can do this somehow using JOIN? I can try that sql syntax also in Hive to see whether it works or not.. – arsenal Jul 07 '12 at 07:07

join two query using SQL

2 Answers2

DEMO