SELECT in a table using NOT EXISTS SQL (SERVER 2012)

Question

I’m trying to make a select tb1 using Where NOT EXISTS (select* tb2) I am trying to select the line in tb1 that does not exist in tb2 to perform an INSERT later.

But when testing in select it is not returning lines that do not exist in tb2.

SELECT *
FROM TB1
WHERE NOT EXISTS(SELECT * FROM tb2)

Answer 1

tb1 need to have some coluna which refers to tb2, even if this is not a constraint (a.ka. foreign key).

So let’s say tb1 have the column tb2_id what reference tb2_id in tb2, you can do so.:

SELECT *
FROM TB1
WHERE NOT EXISTS(SELECT tb2_id FROM tb2 WHERE TB1.tb2_id = tb2.tb2_id)

You can also try.:

SELECT TB1.*
FROM TB1
LEFT JOIN tb2 ON TB1.tb2_id = tb2.tb2_id
WHERE tb2.tb2_id IS NULL

And finally another alternative.:

SELECT *
FROM TB1
WHERE tb2.tb2_id NOT IN (SELECT tb2_id FROM tb2)

In terms of performance, the LEFT JOIN with IS NULL should be the slowest, the difference between the NOT IN and the NOT EXISTS should be marginal. I would stick with the NOT EXISTS for being semantically closer to what it aims to do.

And in case you don’t have one indice for tb2_id in tb1, I advise you to create one, because the NOT EXISTS will be greatly affected by the absence of a.

You can read more on.: Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

EDIT

As you are using a Linked server, try using the OPENQUERY command to query its data.

DECLARE @QUERY_QDE AS NVARCHAR(MAX)
SET @QUERY_QDE = N'SELECT C.[CHAMADO], , C.[COLUNA_1], C.[COLUNA_2], ..., C.[COLUNA_N] FROM [dbo].[TB_QDE_CHAMADO] C';

SELECT CHAMADO_QDE.* 
FROM OPENQUERY([XXX.XXX.XXX.XXX], @QUERY_QDE) AS CHAMADO_QDE
WHERE NOT EXISTS (
    SELECT CHAMADO_AXA.[CHAMADO] 
    FROM [_dbAXA].[dbo].[TB_QDE_CHAMADO] AS CHAMADO_AXA
    WHERE CHAMADO_QDE.[CHAMADO] = CHAMADO_AXA.[CHAMADO]
)

In this case, you will need to specify all columns in your query, because the OPENQUERY doesn’t understand a wildcard.

Finally, [XXX.XXX.XXX.XXX] is the address of your LINKED SERVER

Answer 2

Yeah, you’re trying to select lines in tb1 that don’t exist in tb2, but that’s not what you wrote. What you wrote was to select lines from tb1 when there is no line in tb2 (NOT EXISTS (SELECT * FROM tb2)).

What you need is to identify in your sub-allowance how you identify that the line of tb2 is "equal" to the line in tb1. For example:

select *
  from tb1
 where not exists (
        select *
          from tb2
         where tb2.tb2_id = tb1.tb1_id
       )

or something similar. Now the subconsultation will bring the lines of tb2 that are "equal" (in this case, via comparison of the columns tb1_id and tb2_id) to the tb1 line which the select main is considering at the moment. And then she does what you want.

Answer 3

Missing to relate a row to another row of the other table.

It should be something like that and assuming that the id is the key to each table:

SELECT 
*
FROM 
  TB1 t1
WHERE 
  NOT EXISTS(SELECT * FROM tb2 t2 where t1.id = t2.id)

This way the select will return lines of t1 that do not exist in t2.