Query in postgresql

Question

I have two tables t1 and t2 as following

t1

A    B    C    D   E
1    2    c    d    e
3    1    d    e    f
4    2    f    g    h

t2

Here A,B,C,D,E are the columns of t1 and A,B are the columns of t2 where A and B are common columns.
What I have done so far
I have written the following query

WITH temp as (
    select * 
    from t2
) 
select tab1.* 
from t1 tab1, temp tab2 
where (tab1.A!=tab2.A OR tab1.B!=tab2.B)

I wanted this output

A    B    C    D    E
3    1    d    e    f

But I am getting this output

A    B    C    D    E
1    2    c    d    e
1    2    c    d    e
3    1    d    e    f
3    1    d    e    f
3    1    d    e    f
4    2    f    g    h
4    2    f    g    h

What query should I use?

mitchnull · Accepted Answer · 2012-06-20 10:28:07Z

3

If I understand you correctly, you'd like those rows from T1 that don't have corresponding rows in T2. The easiest way in my opinion is a LEFT OUTER JOIN:

psql=> select * from t1;
 a | b | c | d | e 
---+---+---+---+---
 1 | 2 | c | d | e
 3 | 1 | d | e | f
 4 | 2 | f | g | h
(3 rows)

psql=> select * from t2;
 a | b 
---+---
 1 | 2
 8 | 6
 4 | 2
(3 rows)

psql=> select t1.a, t1.b, t1.c, t1.d, t1.e from t1 left outer join t2 on (t1.a = t2.a and t1.b = t2.b) where t2.a is null;
 a | b | c | d | e 
---+---+---+---+---
 3 | 1 | d | e | f
(1 row)

Edit: Here's the select without the where clause, with the rows from t2 added (otherwise it'd be just like a select * from t1). As you can see, the first row contains NULLs for t2_a and t2_b:

psql=> select t1.a, t1.b, t1.c, t1.d, t1.e, t2.a as t2_a, t2.b as t2_b from t1 left outer join t2 on (t1.a = t2.a and t1.b = t2.b);
 a | b | c | d | e | t2_a | t2_b 
---+---+---+---+---+------+------
 3 | 1 | d | e | f |      |     
 1 | 2 | c | d | e |    1 |    2
 4 | 2 | f | g | h |    4 |    2
(3 rows)

edited Jun 20, 2012 at 10:28

answered Jun 20, 2012 at 7:57

mitchnull

6,3612 gold badges34 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

suraj Over a year ago

It doesnt return any row :( Are u sure the query works fine? Can u tell how this query will be executed?

mitchnull Over a year ago

The paste above is from an actual psql session, so it definitely works for the test data you provided. The left outer join will add NULL instead of real data if the second table doesn't contain a matching row (based on the ON (t1.a = t2.a and t1.b = t2.b) condition).

suraj Over a year ago

Pardon me. It works actually. Can u please show the output after the left outer join without "t2.a is null" constraint? I am trying to understand the query.

dslh · Accepted Answer · 2012-06-20 09:24:13Z

0

How about:

SELECT * FROM t1 WHERE (a,b) NOT IN (SELECT a,b FROM t2);

answered Jun 20, 2012 at 9:24

dslh

1,83514 silver badges19 bronze badges

Collectives™ on Stack Overflow

Query in postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related