Working with a database using PostgreSQL DBMS, where I need to compare tables, eg: table_janeiro and table_February.
I need to perform two queries ...
1st Should return the values contained in the table_janeiro and do not contain in the table_February.
2nd It should return the values contained in the February_table and do not contain in the january_table. (Type or inverse kkk)
Thanks in advance for your help!
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 LEFT JOIN with a condition in the WHERE clause by testing whether 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;
Output:
| id | txt |
|----|----------|
| 4 | DELTA |
| 5 | EPISILON |
The same query can be used to do the inverse:
-- 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;
Output:
| id | txt |
|----|-------|
| 4 | SIGMA |
| 5 | OMEGA |
SQLFiddle: link
Search for the EXCEPT operator. link
"EXCEPT returns all rows that are in the result of query1 but not in the result of query2." Again, duplicates are deleted unless EXCEPT ALL is used. "