CODEWITHSUNDEEP
sqlpostgresqlsubquery
user1561346
user1561346

Reputation: 502

Compare subquery result with pair of 2 parameters

I need to compare 2 parameters in select query with 2 parameters in subquery. Example: 

SELECT * 
FROM pupil 
WHERE name NOT IN (SELECT name FROM bad_names)

This example contains only one parameter to compare. But what if I need to compare subquery result with pair of 2 parameters? This will work:

SELECT * 
FROM pupil 
WHERE name||lastname NOT IN (
    SELECT name||lastname FROM bad_name_lastname_combinations)

So string concatenation is the only way to do this?

Upvotes: 3

Views: 6358

Answers (4)

GercoOnline
GercoOnline

Reputation: 291

You can use the NOT EXISTS command

SELECT * 
FROM pupil p
WHERE NOT EXISTS 
(
  SELECT 1
  FROM bad_name_lastname_combinations b
  WHERE b.name = p.name AND b.lastname = p.lastname)
)

Upvotes: 0

mu is too short
mu is too short

Reputation: 434685

PostgreSQL is flexible enough to do this with a row constructor and NOT IN:

row_constructor NOT IN (subquery)

The left-hand side of this form of NOT IN is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of NOT IN is "true" if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is "false" if any equal row is found.

So you can do it in the straight forward way if you write the row_constructor properly:

select *
from pupil
where (name, lastname) not in (
  select name, lastname
  from bad_last_name_combinations
)

Demo: http://sqlfiddle.com/#!15/a6863/1

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this with a left outer join:

SELECT p.*
FROM pupil p left outer join
     bad_name_lastname_combinations bnlc
     on p.name = bnlc.name and p.lastname = bnlc.lastname
WHERE bnlc.name is null;

Upvotes: 4

Andomar
Andomar

Reputation: 238116

select  *
from    pupil p
where   not exists
        (
        select  *
        from    bad_names bn
        where   bn.name = p.name
                and bn.lastname = p.lastname
        )

Upvotes: 2

Related Questions