Search Records that are not in another table

Asked

Viewed 2,053 times

0

Good morning..

I have the table [BCT_PESSOA] with PK [ID_PESSOA] this table has relationship with the table [BCS_USUARIO] with the column [ID_PESSOA]

I would like to return all records to the table [BCT_PESSOA] that the [ID_PESSOA] do not exist in the table [BCS_USUARIO].

sql sql-server

  • In terms of set theory, what it requires is C = A - B

2 answers

2

Follow an example for the query.

   Select a.*
     from BCT_PESSOA a
left join BCS_USUARIO b on a.ID_PESSOA = b.ID_PESSOA
    where b.ID_PESSOA is null

  • There are some ways to achieve the result, but in my opinion its shape is the most elegant. Using a NOT EXISTS seems too verbose and pollutes SQL.

-1


SELECT * FROM BCT_PESSOA p
inner join BCT_PESSOATIPO pt on pt.ID_PESSOA = p.ID_PESSOA
WHERE NOT EXISTS (SELECT * FROM BCS_USUARIO u WHERE p.ID_PESSOA = u.ID_PESSOA)
and pt.ST_TIPO = 'FUN'

I think that solves

  • In terms of set theory, what it requires is C = A - B. There are some ways to implement in SQL, and the use of WHERE not exists() is one of them.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.