SQL query that compares two tables and returns values

Asked

Viewed 2,415 times

0

Work with a database using with DBMS Postgresql, where I need to compare tables, example: table_janeiro and table_February.

I need to perform two consultations...

first You must return the values that contain in the January table and do not contain in the February table.

2nd You must return the values that contain in the table_February and do not contain in table_January. (Type the inverse kkk)

Thanks for your help !

sql database postgresql query comparison

2 answers

1

Assuming your data structure is something like:

CREATE TABLE tb_janeiro
(
    id BIGINT NOT NULL,
    txt TEXT NOT NULL,
    PRIMARY KEY( id )
);

CREATE TABLE tb_fevereiro
(
    id BIGINT NOT NULL,
    txt TEXT NOT NULL,
    PRIMARY KEY( id )
);

Containing the following records:

-- INSERE REGISTROS EM JANEIRO
INSERT INTO tb_janeiro ( id, txt ) VALUES ( 1, 'ALPHA' );
INSERT INTO tb_janeiro ( id, txt ) VALUES ( 2, 'BETA' );
INSERT INTO tb_janeiro ( id, txt ) VALUES ( 3, 'GAMMA' );
INSERT INTO tb_janeiro ( id, txt ) VALUES ( 4, 'DELTA' );
INSERT INTO tb_janeiro ( id, txt ) VALUES ( 5, 'EPISILON' );

-- INSERE REGISTROS EM FEVEREIRO
INSERT INTO tb_fevereiro ( id, txt ) VALUES ( 1, 'ALPHA' );
INSERT INTO tb_fevereiro ( id, txt ) VALUES ( 2, 'BETA' );
INSERT INTO tb_fevereiro ( id, txt ) VALUES ( 3, 'GAMMA' );
INSERT INTO tb_fevereiro ( id, txt ) VALUES ( 4, 'SIGMA' );
INSERT INTO tb_fevereiro ( id, txt ) VALUES ( 5, 'OMEGA' );

You can use a LEFT JOIN with one condition in the clause WHERE testing if the record does not exist in the other table, for example:

-- RECUPERA REGISTROS CONTIDOS EM JANEIRO QUE NÃO ESTAO CONTIDOS NA EM FEVEREIRO
SELECT
    jan.id,
    jan.txt
FROM
    tb_janeiro AS jan
LEFT JOIN
    tb_fevereiro AS fev ON ( jan.txt = fev.txt )
WHERE
    fev.id IS NULL;

Exit:

| id |      txt |
|----|----------|
|  4 |    DELTA |
|  5 | EPISILON |

The same query can be used to do the reverse: 

-- RECUPERA REGISTROS CONTIDOS EM FEVEREIRO QUE NÃO ESTAO CONTIDOS NA EM JANEIRO
SELECT
    fev.id,
    fev.txt
FROM
    tb_fevereiro AS fev
LEFT JOIN
    tb_janeiro AS jan ON ( jan.txt = fev.txt )
WHERE
    jan.id IS NULL;

Exit:

| id |   txt |
|----|-------|
|  4 | SIGMA |
|  5 | OMEGA |

Sqlfiddle: http://sqlfiddle.com/#! 9/557e04/1

0

Browser other questions tagged sql database postgresql query comparison

You are not signed in. Login or sign up in order to post.