My left_table has 400 rows and my right_table has 100 rows. The ID column for the left_table is id_left and the ID column for the right_table is id_right.

SELECT * FROM left_table 
WHERE id_left IN (SELECT id_right FROM right_table); 

I have 100 rows in the results, which means the right_table has 100 rows that are aslo in the left_table.

Then, I want to do a left join using the following commands:

SELECT l.id_left, l.A, l.B
FROM left_table AS l 
INNER JOIN (
    SELECT id_right, C,D
    FROM right_table )
AS  r ON (l.id_left = r.id_right);

I expect to get a table with 100 rows including columns id_left, id_right, A, B, C and D.

However, in the final results, I only got 100 rows, which is expected with id_left, A, B (all selected columns from the left_table. I want to get some help to trouble shooting on what I did wrong so the final results do not have columns selected from the right_table.

Thanks a lot.