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

1

I'm trying to make a Select tb1 using Where NOT EXISTS (select* tb2) I'm trying to select the row in tb1 that does not exist in tb2 to INSERT later.

However when doing test on select it is not returning the lines that do not exist in tb2.

SELECT *
FROM TB1
WHERE NOT EXISTS(SELECT * FROM tb2)
    
asked by anonymous 14.06.2017 / 18:45

3 answers

3

tb1 must have some coluna that references tb2 , even if it is not a constraint (a.k.a foreign key ).

Then let's say that tb1 has the tb2_id column that references 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,% w / w% w / w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w w. I would have LEFT JOIN because it is semantically closer to what I intend to do.

And if you do not have IS NULL to NOT IN in NOT EXISTS , I advise you to create one, since NOT EXISTS will be greatly affected by the absence of one.

You can read more on: indice

EDIT

Since you are using a linked server, try using the OPENQUERY command to query the linked server's 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 have to specify all the columns in your query, since tb2_id does not understand a wildcard.

Finally, tb1 is the address of your NOT EXISTS

    
14.06.2017 / 19:03
2

Yeah, you're trying to select rows in tb1 that do not exist in tb2, but that's not what you wrote. What you wrote was to select rows from tb1 when there are no rows in tb2 ( NOT EXISTS (SELECT * FROM tb2) ).

What you need is to identify in your subquery how you identify the line of tb2 is "equal" to the line at tb1. For example:

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

or something similar. Now the subquery will bring the lines of tb2 that are "equal" (in this case, via comparison of the columns tb1_id and tb2_id ) to the line of tb1 that select principal is currently considering. And then it does what you want.

    
14.06.2017 / 19:04
1

It is missing to relate one row to another row of the other table.

It should be something like this and assuming id is the key for each table:

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

In this way select will return lines from t1 that do not exist in t2.

    
14.06.2017 / 19:00