Join between banks

Question

Staff I am running the command below but I haven’t gotten the desired result.

USE banco1

SELECT * 
  FROM sistema.tb_menus_perfil_params a  
  LEFT JOIN banco2.sistema.tb_menus_perfil_params b 
      ON a.pm_id = b.pm_id  
  where a.mnp_id = 10
        and b.mnp_id = 9

The result I imagine to have are the COMMON records of both sources and the records of to who are not in b but I am only shown the records in common.

What could be wrong?

When I consult separately:

When I consult with left Join:

Note that in banco1 there is the record 266 in the pm_id field and this value does not exist in banco2. So why does this record not appear when I use left Join?

Answer 1

You can run this way without Where :

SELECT a.pm_id AS banco1, 
       b.pm_id AS banco2 
FROM sistema.tb_menus_perfil_params a(NOLOCK) 
    LEFT JOIN banco2.sistema.bdo.tb_menus_perfil_params b (NOLOCK) ON a.pm_id = b.pm_id

This way you can check the common results and those that do not exist in one table of the x bank and the other not. Since you don’t even have a rule for it, you can run without Where.

Answer 2

The result I imagine to have are the COMMON records of both sources and the records of a who are not in b but I am only shown the records in common.

In the case of LEFT JOIN joins care should be taken when using "right table" columns in the WHERE clause. Otherwise, LEFT JOIN is transformed internally into INNER JOIN.

The following excerpt in your code:

and b.mnp_id = 9

made LEFT JOIN become INNER JOIN, then disappeared with the 266 and others...

Try:

-- código #1
SELECT A.colunas, B.colunas 
  from banco1.sistema.tb_menus_perfil_params as A  
  left join banco2.sistema.tb_menus_perfil_params as B 
         on A.pm_id = B.pm_id  
  where A.mnp_id = 10
        and (B.mnp_id = 9 or B.pm_id is null);

Other option:

-- código #2
SELECT A.colunas, B.colunas 
  from banco1.sistema.tb_menus_perfil_params as A  
  left join banco2.sistema.tb_menus_perfil_params as B 
         on A.pm_id = B.pm_id  
            and B.mnp_id = 9
  where A.mnp_id = 10;

And one more option:

-- código #3
with 
B1_tb_menus_perfil_params as (    
SELECT *
  from banco1.sistema.tb_menus_perfil_params
  where mnp_id = 10
),
B2_tb_menus_perfil_params as (    
SELECT *
  from banco2.sistema.tb_menus_perfil_params
  where mnp_id = 9
)
SELECT A.colunas, B.colunas 
  from B1_tb_menus_perfil_params as A  
  left join B2_tb_menus_perfil_params as B 
         on A.pm_id = B.pm_id;

In the 3 codes above you must replace colunas by the name of the columns you need.

Answer 3

To have the registers in Comun and the A

SELECT a.pm_id, b.pm_id
  FROM sistema.tb_menus_perfil_params a  WITH (NOLOCK)
LEFT JOIN banco2.sistema.tb_menus_perfil_params b WITH (NOLOCK)
    ON a.pm_id = b.pm_id

I also added the parameter WITH (NOLOCK) which you should consider using. The SELECT command allows the use of this NOLOCK option, thus avoiding blockages with INSERT commands.