Join two similar tables

Question

This is Table1 data.

BUYER_ID     |    ITEM_ID         |      CREATED_TIME 
-------------+--------------------+------------------------
1015826235       220003038067            2001-11-03 19:40:21
1015826235       300003861266            2001-11-08 18:19:59
1015826235       140002997245            2003-08-22 09:23:17
1015826235       200002448035            2001-11-11 22:21:11
1015826235      *260003553381*          *2005-05-27 07:09:56*
1015826235      *260003553382*          *2002-02-02 19:40:39*
2012926235      *260003553383*          *2002-06-01 06:58:47*

If you compare the Table1 data with the below Table2 data, then the last three lines are missing in Table2 data after comparison from Table1 data for particular USER_ID with BUYER_ID.

USER_ID     |    PRODUCT_ID    |   TIMESTAMPS
------------+------------------+-------------
1015826235       220003038067      1004841621
1015826235       300003861266      1005268799
1015826235       140002997245      1061569397
1015826235       200002448035      1005542471

So I need to show either of the two result like this for the above example after JOINING Table1 with Table2-

BUYER_ID   |     ITEM_ID       |    CREATED_TIME         |     USER_ID     |       PRODUCT_ID     |   TIMESTAMPS
-----------+-------------------+-------------------------+-----------------+----------------------+------------------
1015826235       260003553381       2005-05-27 07:09:56       1015826235          NULL              NULL
1015826235       260003553382       2002-02-02 19:40:39       1015826235          NULL              NULL 
2012926235       260003553383       2002-06-01 06:58:47       2012926235          NULL              NULL

OR

BUYER_ID   |     ITEM_ID       |    CREATED_TIME         |    PRODUCT_ID  |   TIMESTAMPS
-----------+-------------------+-------------------------+----------------+--------------
1015826235       260003553381       2005-05-27 07:09:56         NULL              NULL
1015826235       260003553382       2002-02-02 19:40:39         NULL              NULL 
2012926235       260003553383       2002-06-01 06:58:47         NULL              NULL

Any suggestion will be appreciated.

dash · Accepted Answer · 2012-07-08T22:00:53.857

2

I believe what you are after is the LEFT JOIN operator;

SELECT * 
FROM Table1 
LEFT JOIN Table2 ON Table1.ITEM_ID = TABLE2.PRODUCT_ID AND Table1.BUYER_ID = Table2.USER_ID 
WHERE Table2.PRODUCT_ID IS NULL

What we are effectively saying is "Bring me back all rows from table 2 that have a matching product AND user id, and all those that don't (the LEFT JOIN bit). Then only show me those things that don't have a match in Table 2 (the IS NULL bit)."

Joins are explained neatly here:

Jeff Atwood's Visual Guide to SQL Joins

edited Jul 08 '12 at 22:00

answered Jul 08 '12 at 21:55

dash

89,546
4
51
71

Thanks dash, yes that worked. I have posted one more question on SO, in which nobody has replied yet. [http://stackoverflow.com/questions/11386368/sql-query-join-with-table](http://stackoverflow.com/questions/11386368/sql-query-join-with-table). Can you please take a look into that also. It will be of great help to me. – arsenal Jul 08 '12 at 22:39

score 1 · Answer 2 · answered Jul 08 '12 at 21:57

1

You just need to use a LEFT JOIN, which will include rows from the source table even if there is no match in the joined table, selecting NULL for fields that would come from it.

SELECT Table1.BUYER_ID
     , Table1.ITEM_ID
     , Table1.CREATED_TIME
     , Table2.USER_ID
     , Table2.PRODUCT_ID
     , Table2.TIMESTAMPS
  FROM Table1
       LEFT JOIN Table2 ON Table1.BUYER_ID = Table2.USER_ID

You can filter the above to rows that are missing in Table2 (to get your example) by adding

 WHERE BUYER_ID NOT IN (SELECT USER_ID FROM Table2)

Or, remove the NOT to get records where there is a match (your second example).

(You can also use the WHERE PRODUCT_ID = NULL as @dash suggested, but it does not express quite the same intent.)

answered Jul 08 '12 at 21:57

harpo

41,820
13
96
131

I agree, but, for efficiency sake, I'd prefer to avoid a correlated sub query. I like the way the NULL check is more a set operation than a data operation though :-) – dash Jul 08 '12 at 22:04
But the only problem with this is, for Table2.USER_ID I am getting NULL always. – arsenal Jul 08 '12 at 22:30
Thanks harpo, yes that worked like a charm but the only one problem that I posted above. Thanks for the solution. I have posted one more question on SO, in which nobody has replied yet. [http://stackoverflow.com/questions/11386368/sql-query-join-with-table](http://stackoverflow.com/questions/11386368/sql-query-join-with-table). Can you please take a look into that also. It will be of great help to me. – arsenal Jul 08 '12 at 22:31

Join two similar tables

2 Answers2