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