Swap subselect with Oin

Question

good morning!

I’m sorry if explanation is not totally clear, it’s because I’m starting in SQL.

How do I replace subselect with some type of Join? Because subselect takes longer to load.

Examples:

The statement below is bringing Ids (repeats) that contain 123-value records in table A, but only Ids that do not contain 456 in the same column of table B. (the tables are the same)

SELECT DISTINCT A.ID FROM BANCO..TABELA1 A
WHERE A.COLUNA1 = '123'
    AND A.ID NOT IN (
        SELECT B.ID FROM BANCO..TABELA1 B
            WHERE B.COLUNA1 = '456')
ORDER BY A.ID

I tried to do it this way, but it didn’t work

SELECT DISTINCT A.ID FROM PlanMisStg..TB_AUX_MOD_OFERTAS A
    LEFT JOIN PlanMisStg..TB_AUX_MOD_OFERTAS B
        ON A.TECNOLOGIA = B.TECNOLOGIA
            AND A.SEGMENTO = 'FTTH'
WHERE B.SEGMENTO NOT IN ('FTTH BENCH')
ORDER BY A.ID

Answer 1

FELIPE, follows the idea of how to do JOIN

SELECT DISTINCT A.ID FROM BANCO..TABELA1 A
JOIN TABELA1 B ON A.id = B.iD
WHERE A.COLUNA = '123' AND B.ID != 456
ORDER BY A.ID